Display Union Data Side By Side Without Duplication

user_14126758 0
Viewed 95

I am trying to display the total sale and number of customers on a particular month based on the age group. I am able to display the result as follows:

Result

But my desired output is like this:

Age Group      Number of Customers       Total Sales
----------------------------------------------------
Teenager             57                   48313.26
Adult                277                 198512.89
Senior Citizen       50                      44770

I already tried several method like inline select and union all/union but fail to produce the desired output. My coding for the result is below:

SELECT CASE
      WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 50 ) THEN 'Senior Citizen'
      WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 25 ) THEN 'Adult'
      ELSE 'Teenager'
    END AS "AGE GROUP",
    COUNT(*) AS "NUMBER OF CUSTOMER"
FROM customer
GROUP BY CASE
        WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 50 ) THEN 'Senior Citizen'
        WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 25 ) THEN 'Adult'
        ELSE 'Teenager'
    END
UNION ALL
SELECT CASE
      WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 50 ) THEN 'Senior Citizen'
      WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 25 ) THEN 'Adult'
      ELSE 'Teenager'
   END AS "AGE GROUP",
   SUM(final_total) AS "TOTAL SALES"
FROM customer c, orders o
WHERE c.cust_id = o.cust_id
AND EXTRACT(YEAR FROM order_datetime) = &check_year            
GROUP BY CASE
        WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 50 ) THEN 'Senior Citizen'
        WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 25 ) THEN 'Adult'
        ELSE 'Teenager'
    END;

EDIT :

Thanks to Mureinik and Thorsten Kettner helps, I finally get the desired output and know that I didn't look enough documentation haha. My try of coding:

SELECT CASE
      WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 50 ) THEN 'Senior Citizen'
      WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 25 ) THEN 'Adult'
      ELSE 'Teenager'
    END AS "AGE GROUP",
    COUNT(DISTINCT c.cust_id) AS "NUMBER OF CUSTOMER",
    SUM(final_total) AS "TOTAL SALES"
FROM customer c, orders o
WHERE c.cust_id = o.cust_id
AND EXTRACT(YEAR FROM order_datetime) = &check_year            
GROUP BY CASE
        WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 50 ) THEN 'Senior Citizen'
        WHEN (EXTRACT(Year From (SYSDATE - dob) YEAR TO MONTH) > 25 ) THEN 'Adult'
        ELSE 'Teenager'
    END;

I feel so dumb that I never think of putting DISTINCT inside the COUNT(). But still, Mureinik code is also working for me!

select group-by oracle sql
2 Answers

You can query both aggregate terms from a join on the two tables. Note that a left join is preferable here in case you have customers without orders (not sure if this is possible in your data, but just in case):

SELECT   CASE WHEN (EXTRACT(Year From (SYSDATE - c.dob) YEAR TO MONTH) > 50 ) THEN 'Senior Citizen'
              WHEN (EXTRACT(Year From (SYSDATE - c.dob) YEAR TO MONTH) > 25 ) THEN 'Adult'
              ELSE 'Teenager'
              END AS "AGE GROUP",
          COUNT(DISTINCT c.cust_id) AS "NUMBER OF CUSTOMER",
          SUM(o.final_total) AS "TOTAL SALES"
FROM      customer c
LEFT JOIN orders o ON c.cust_id = o.cust_id
                   AND EXTRACT(YEAR FROM o.order_datetime) = &check_year
GROUP BY  CASE WHEN (EXTRACT(Year From (SYSDATE - c.dob) YEAR TO MONTH) > 50 ) THEN 'Senior Citizen'
               WHEN (EXTRACT(Year From (SYSDATE - c.dob) YEAR TO MONTH) > 25 ) THEN 'Adult'
               ELSE 'Teenager'
          END;
user_24227760

May I suggest to use the WITH clause to prevent duplicate complexity?

Edited the original code to show results of two years.

WITH age_groups as (
  SELECT   CASE WHEN (EXTRACT(Year From (SYSDATE - c.dob) YEAR TO MONTH) > 50 ) THEN 'Senior 
  Citizen'
              WHEN (EXTRACT(Year From (SYSDATE - c.dob) YEAR TO MONTH) > 25 ) THEN 'Adult'
              ELSE 'Teenager'
              END AS AGE_GROUP
              , c.cust_id, o.final_total
              , extract(YEAR from o.order_datetime) as year
  FROM      customer c
  LEFT JOIN orders o ON c.cust_id = o.cust_id
),
year1 as (
select    age_group ,
          COUNT(DISTINCT cust_id) AS NUMBER_OF_CUSTOMERS_Y1,
          SUM(final_total) AS TOTAL_SALES_Y1
FROM      age_groups
WHERE     year = &check_year1
group by age_group),
year2 as (select    age_group ,
          COUNT(DISTINCT cust_id) AS NUMBER_OF_CUSTOMERS_Y2,
          SUM(final_total) AS TOTAL_SALES_Y2
FROM      age_groups
WHERE     year = &check_year2
group by age_group)
select age_group, number_of_customers_y1, total_sales_y1
                , number_of_customers_y2, total_sales_y2
from year1 join year2 using(age_group);
user_141477640
Related