-1

I have a set of data in my database - see below example:

Food      CONSUMPTION  DATE_INSERTED
--------  -----------  -------------
Burger              2  07/10/2021
Fries               1  08/10/2021
Burger              2  08/10/2021
Chicken             1  07/12/2021
Burger              1  08/15/2021
Fries               2  08/11/2021

With this, I want to create a query like that will display this output:

Food      July  August  September  October
--------  ----  ------  ---------  -------
Burger       2       3          0        0
Fries        0       3          0        0
Chicken      1       0          0        0

I actually did this in SQL Server but I am having a hard time to display the output in Oracle.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Alexander Rodriguez
  • 9
  • 1

1 Answers1

1

Here's how.

SQL> select food,
  2    sum(case when to_char(date_inserted, 'mm') = '07' then consumption else 0 end) as July,
  3    sum(case when to_char(date_inserted, 'mm') = '08' then consumption else 0 end) as August,
  4    sum(case when to_char(date_inserted, 'mm') = '09' then consumption else 0 end) as September,
  5    sum(case when to_char(date_inserted, 'mm') = '10' then consumption else 0 end) as October
  6  from food
  7  group by food
  8  order by food;

FOOD             JULY     AUGUST  SEPTEMBER    OCTOBER
---------- ---------- ---------- ---------- ----------
Burger              2          3          0          0
Chicken             1          0          0          0
Fries               0          3          0          0

SQL>

Add other months as well, if you want/have to.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57