Nested Query in ORACLE

[sammytd2]

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??

[andrejm]

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

[chrisrlong]

Never go into a table more than once if you don't have to.

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

[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 ??

[chrisrlong]

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

[onlysimon]

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

[chrisrlong]

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

[chrisrlong]

Well, in any case, the solution you posted there was much better than the original anyway. And here is an even better version:

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

[pando]

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?