-
Nested Query in ORACLE
Nested query is kicking my, well I won't say.
Here is the SQL: explanation of what I am trying to do follows the SQL:
select a.warehouse,
count(1) NUM_LINES, (select count(1) as zero_bal
FROM inventory b where b.warehouse='ACE' and
b.Q_RO > 0 and b.q_oh=0)
FROM inventory a WHERE a.warehouse='ACE'
and a.q_RO > 0 GROUP BY a.warehouse;
What I am trying (very uncessfully) is to get counts from a single table where
different conditions are met. In this eaample, I need to get acount of lines
in warehouse ACE where (Q_RO > 0) and a count of all the zero balance or Quanity on hand = 0. (Q_OH=0)
I am expecting something like
WAREHOUSE NUM_LINES ZERO_BAL
----------------------------------
ACE 960 27
I keep getting a not a group by Expression like below.
count(1) NUM_LINES, (select count(1) as zero_bal
*
ERROR at line 2:
ORA-00979: not a GROUP BY expression
Can anyone help a feller out??
-
maybe this is what you are looking for
SELECT warehouse,
COUNT(*) NUM_LINES,
(SELECT COUNT(1) OVER( PARTITION BY warehouse )
FROM inventory
WHERE warehouse='ACE'
AND q_RO > 0
AND b.q_oh=0) zero_bal
FROM inventory
WHERE warehouse='ACE'
AND q_RO > 0
Andrej
-
Never go into a table more than once if you don't have to.
Code:
SELECT
WAREHOUSE,
COUNT(*)
AS NUM_LINES,
SUM(
CASE
WHEN
B.Q_OH = 0
THEN
1
END
)
AS ZERO_BAL
FROM
INVENTORY
WHERE
WAREHOUSE = :WAREHOUSE AND
Q_RO > 0
GROUP BY
WAREHOUSE ;
- Chris
Last edited by chrisrlong; 04-09-2003 at 06:27 PM.
-
Select count(*) will go for full table scan. If it is very huge table it may take more time. Is rewriting SQL with select count(1) is better or ??
Raghu
-
Man, but this one never dies....
COUNT(*) is the accepted standard and there is ABSOLUTELY NO DIFFERENCE IN PERFORMANCE OR PLAN between COUNT(1) and COUNT(*).
Period.
- Chris
-
I'm having a similar problem. This works fine in 9i (accessing 8i through a database link) but not in 8i.
code:
select a.row_owner, max(b.full_name), count(1) total_rows, max(c.period_total) "6 MONTH UPDATES", max(d.period_total) "12 MONTH UPDATES" from accounts a, row_owner_table b, (select count(owner_id) period_total, owner_id from accounts where last_update > ( sysdate - 180) group by owner_id ) c, (select count(prd_ow_i) period_total, owner_id from accounts where last_update > ( sysdate - 360) group by owner_id ) d where b.id = a.row_owner and d.owner_id (+) = a.owner_id and c.owner_id (+) = a.owner_id group by a.owner_id order by count(1) desc;
I managed to rewrite it using decode()/greater()/sum() but now I have to do another query where I need to use 2 group by's in a subquery.
(see also http://saloon.javaranch.com/cgi-bin/...&f=55&t=000417)
Anybody got some other suggestions on this type of query?
Simon
-
Question about the solution you posted on the other forum...
- Why are you doing max(A.location)? Is this table simply not designed correctly?
- Chris
-
Well, in any case, the solution you posted there was much better than the original anyway. And here is an even better version:
Code:
SELECT
A.USER_ID ID ,
ROT.FULL_NAME FULL_NAME ,
A.LOCATION LOCATION ,
A.PAST_WEEK WEEK ,
A.PAST_MONTH MONTH ,
A.PAST_QUARTER QUARTER ,
A.PAST_YEAR YEAR ,
A.TOTAL_DAYS TOTAL
FROM
(
SELECT
USER_ID ,
MAX(LOCATION)
AS LOCATION ,
COUNT(*)
AS TOTAL_DAYS ,
SUM(
CASE WHEN LAST_UPDATE >= SYSDATE - 7 THEN 1 END
)
AS PAST_WEEK ,
SUM(
CASE WHEN LAST_UPDATE >= ADD_MONTHS(SYSDATE, -1) THEN 1 END
)
AS PAST_MONTH ,
SUM(
CASE WHEN LAST_UPDATE >= ADD_MONTHS(SYSDATE, -3) THEN 1 END
)
AS PAST_QUARTER ,
SUM(
CASE WHEN LAST_UPDATE >= ADD_MONTHS(SYSDATE, -12) THEN 1 END
)
AS PAST_YEAR
FROM
ACCOUNTS
WHERE
LAST_UPDATE >= ADD_MONTHS(SYSDATE, -12)
GROUP BY
USER_ID
) A ,
ROW_OWNER_TABLE ROT
WHERE
ROT.USER_ID = A.USER_ID
ORDER BY
PAST_WEEK ,
PAST_MONTH
This version will only hit the Accounts table once, much like your second statement, but I made further changes:
- DECODE -> CASE
...- CASE is more readable
...- CASE is faster
- Moved GROUP BY
...- I waited until after the GROUP BY to join to the ROT table, saving time
- Added WHERE clause
...- Since you are only interested in records from the past year, this predicate will make it faster.
- Used ADD_MONTHS, which is more precise, based on your final names. You called the field MONTH, but it was actually the past 30 days, which is not always a month.
You should still look into that MAX(LOCATION) thing
- Chris
-
Originally posted by raghud
Select count(*) will go for full table scan. If it is very huge table it may take more time. Is rewriting SQL with select count(1) is better or ??
tell us why count(1) would go faster?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|