10 Replies Latest reply: Nov 14, 2012 2:44 PM by madmac RSS

    union dates and count(*) from different tables

    madmac
      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
          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
            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
              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
                madmac
                Thank you both for the speedy reply. Awesome code!
                • 5. Re: union dates and count(*) from different tables
                  madmac
                  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
                    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
                      madmac
                      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
                        madmac
                        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
                          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
                            madmac
                            Fixed it. I selected unique(coalesce(a.dt,b.dt,c.dt,d.dt)) dt, Thanks