2

I want to get the following output

enrollment_month weekend_revenue weekday_revenue
jun 550 790
may 1570 2020

this is the code I have:

select g.enrollment_month, sum(g.total_revenue) as weekday_revenue 
from gracieMusicFact g
where g.lesson_day in ('Monday','Tuesday','Wednesday','Thursday','Friday')
group by enrollment_month;

select g.enrollment_month, sum(g.total_revenue) as weekend_revenue 
from gracieMusicFact g
where g.lesson_day in ('Saturday','Sunday')
group by enrollment_month;

Union only gives me either one of the weekend_revenue or weekday_revenue. How do I make two different select statements with different criteria?

Thanks

Edit: Using union all gives me this outpu:

| enrollment_month   | weekday_revenue |

| jun|  790 |
| may| 2020 |
| jun|  550 |
| may| 1570 |
Roberto Hernandez
  • 8,231
  • 3
  • 14
  • 43
oraclehelpplease
  • 21
  • 3

2 Answers2

3

Use conditional aggregation:

select g.enrollment_month,
       sum(case when g.lesson_day in ('Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday') then g.total_revenue else 0 end) as weekday_revenue,
       sum(case when g.lesson_day in ('Saturday', 'Sunday') then g.total_revenue else 0 end) as weekend_revenue
from gracieMusicFact g
group by enrollment_month;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I think you would need to use UNION ALL

The only difference between Union and Union All is that Union All will not removes duplicate rows or records, instead, it just selects all the rows from all the tables which meets the conditions of your specifics query and combines them into the result table. ... Whereas, UNION ALL works with all data type columns.

What is the difference between UNION and UNION ALL?

TuringMachine
  • 46
  • 2