This discussion is archived
10 Replies Latest reply: Nov 14, 2012 12:44 PM by 899569 RSS

union dates and count(*) from different tables

899569 Newbie
Currently Being Moderated
Hope you can help. Using Oracle Database 11g Enterprise Edition Release 11.2.0.1.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')

EXAMPLE DATA
Data for apples

2012-01-01 4
2012-04-23 1

Data for oranges
2012-02-13 5
2012-03-11 2

Data for pears
2012-01-01 11

I would like the output to be

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
  • 1. Re: union dates and count(*) from different tables
    sb92075 Guru
    Currently Being Moderated
    madmac wrote:
    Hope you can help. Using Oracle Database 11g Enterprise Edition Release 11.2.0.1.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')

    EXAMPLE DATA
    Data for apples

    2012-01-01 4
    2012-04-23 1

    Data for oranges
    2012-02-13 5
    2012-03-11 2

    Data for pears
    2012-01-01 11

    I would like the output to be
    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
    How do I ask a question on the forums?
    SQL and PL/SQL FAQ
  • 2. Re: union dates and count(*) from different tables
    Solomon Yakobson Guru
    Currently Being Moderated
    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>  
    SY.
  • 3. Re: union dates and count(*) from different tables
    pollywog Expert
    Currently Being Moderated
    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
  • 4. Re: union dates and count(*) from different tables
    899569 Newbie
    Currently Being Moderated
    Thank you both for the speedy reply. Awesome code!
  • 5. Re: union dates and count(*) from different tables
    899569 Newbie
    Currently Being Moderated
    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)?
  • 6. Re: union dates and count(*) from different tables
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    madmac wrote:
    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)?
    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.
    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
    ;
    Edited by: Frank Kulash on Nov 13, 2012 4:45 PM
  • 7. Re: union dates and count(*) from different tables
    899569 Newbie
    Currently Being Moderated
    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.
  • 8. Re: union dates and count(*) from different tables
    899569 Newbie
    Currently Being Moderated
    Let me clear that up. If I select 2012-10-29 as the date and I have records in there for apples and oranges then I get three lines of output.
  • 9. Re: union dates and count(*) from different tables
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    madmac wrote:
    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.
    Sorry, I don't understand.

    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.
  • 10. Re: union dates and count(*) from different tables
    899569 Newbie
    Currently Being Moderated
    Fixed it. I selected unique(coalesce(a.dt,b.dt,c.dt,d.dt)) dt, Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points