This content has been marked as final. Show 10 replies
Hope you can help. Using Oracle Database 11g Enterprise Edition Release 220.127.116.11.0 - 64bit Production
I have tables called apples, oranges, and pears. I want to get a count of all apples, oranges, pears and a total for a date range in one query statement. Any ideas?
for apples the sql is select count(apple_types) from apples where apple_date > TO_DATE('2012-01-01')
for oranges the sql is select count(orange_types) from orange where orange_date > TO_DATE('2012-01-01')
for pears the sql is s elect count(pear_types) from pears where pears_date > TO_DATE('2012-01-01')
Data for apples
Data for oranges
Data for pears
I would like the output to be
How do I ask a question on the forums?
date count(apple_types) count(orange_types) count(pear_types) total
2012-01-01 4 0 11 15
2012-02-13 0 5 0 5
2012-03-11 0 2 0 2
2012-04-23 1 0 0 1
SQL and PL/SQL FAQ
Use full join:
with apples as ( select date '2012-01-01' dt,4 cnt from dual union all select date '2012-04-23',1 from dual ), oranges as ( select date '2012-02-13' dt,5 cnt from dual union all select date '2012-03-11',2 from dual ), pears as ( select date '2012-01-01' dt,11 cnt from dual ) select coalesce(a.dt,o.dt,p.dt) dt, nvl(a.cnt,0) apple_count, nvl(o.cnt,0) orange_count, nvl(p.cnt,0) pear_count, nvl(a.cnt,0) + nvl(o.cnt,0) + nvl(p.cnt,0) total_count from apples a full join oranges o on a.dt = o.dt full join pears p on coalesce(a.dt,o.dt) = p.dt order by coalesce(a.dt,o.dt,p.dt) / DT APPLE_COUNT ORANGE_COUNT PEAR_COUNT TOTAL_COUNT --------- ----------- ------------ ----------- ----------- 01-JAN-12 4 0 11 15 13-FEB-12 0 5 0 5 11-MAR-12 0 2 0 2 23-APR-12 1 0 0 1 SQL>
here is one with pivot
WITH apples AS (SELECT TO_DATE ('2012-01-01', 'yyyy-mm-dd') dt, 4 cnt FROM DUAL UNION ALL SELECT TO_DATE ('2012-04-23', 'yyyy-mm-dd') dt, 1 cnt FROM DUAL), oranges AS (SELECT TO_DATE ('2012-01-13', 'yyyy-mm-dd') dt, 5 cnt FROM DUAL UNION ALL SELECT TO_DATE ('2012-03-11', 'yyyy-mm-dd') dt, 2 cnt FROM DUAL), pears AS (SELECT TO_DATE ('2012-01-01', 'yyyy-mm-dd') dt, 11 cnt FROM DUAL), t AS ( SELECT dt, SUM (cnt) cnt, 'apples' fruit FROM apples GROUP BY dt UNION ALL SELECT dt, SUM (cnt), 'oranges' FROM oranges GROUP BY dt UNION ALL SELECT dt, SUM (cnt), 'pears' FROM pears GROUP BY dt) SELECT dt, NVL (apples, 0), NVL (oranges, 0), NVL (pears, 0), NVL (apples, 0) + NVL (oranges, 0) + NVL (pears, 0) total FROM t PIVOT (MAX (cnt) FOR fruit IN ('apples' AS apples, 'oranges' AS oranges, 'pears' AS pears)) ORDER BY 1
DT NVL(APPLES,0) NVL(ORANGES,0) NVL(PEARS,0) TOTAL 1/1/2012 4 0 11 15 1/13/2012 0 5 0 5 3/11/2012 0 2 0 2 4/23/2012 1 0 0 1
madmac wrote:Outer-join each of your tables to a table (or, in the example below, a result set) that has one row for every date you want included.
One more thing. What if there was a date range given but no data for a certain date? How would you include that date (showing all 0's)?
Edited by: Frank Kulash on Nov 13, 2012 4:45 PM
VARIABLE first_dt VARCHAR2 (10) VARIABLE last_dt VARCHAR2 (10) EXEC :first_dt := '01/01/2012' EXEC :last_dt := '04/23/2012' WITH all_dates AS ( SELECT TO_DATE (:first_dt, 'MM/DD/YYYY') + LEVEL - 1 AS a_dt FROM dual CONNECT BY LEVEL <= TO_DATE (:last_dt, 'MM/DD/YYYY') + 1 - TO_DATE (:first_dt, 'MM/DD/YYYY') ) SELECT ad.a_dt , NVL (SUM (a.cnt), 0) AS apples , NVL (SUM (o.cnt), 0) AS oranges , NVL (SUM (p.cnt), 0) AS pears FROM all_dates ad LEFT OUTER JOIN apples a ON ad.a_dt = a.dt LEFT OUTER JOIN oranges o ON ad.a_dt = o.dt LEFT OUTER JOIN pears p ON ad.a_dt = p.dt GROUP BY ad.a_dt ORDER BY ad.a_dt ;
madmac wrote:Sorry, I don't understand.
Came across something interesting. If I select one day that doesnt have a pear record then I get 3 lines of output. Why do I get 3 lines intead of 1? I used Solomon's coding technique
Dont know how to recreate this with select from dual.
Solomon's query produced 4 rows of output, even without the new data you added.
Post the exact data you're using, and the exact query, even if you (believe you) just copied it from this site.
Why do you want 1 row of output, and not 3 (or 4)? What do you want on that one row?
Remember, Solomon's solution was in reply to your original question. An hour after Solomon posted it, you made a new request, asking for given dates whether they had any data or not. You can't expect that query to satisfy a requirement you hadn't made yet.