CODEWITHSUNDEEP
sqloracle-databasecountgroup-bysum
user1297532
user1297532

Reputation:

Group by date and count specific values

I'm trying to create a report where there is only one row for each date, and then a separate counter for each color that the customers has chosen.

The input data consists of two columns, one for date, and one for color. like this:

2014-04-11  BLUE

2014-04-11  GREEN

2014-04-11  BLUE

2014-04-10  RED

2014-04-10  GREEN

2014-04-09  RED

2014-04-09  RED

This is what i would like the report to look like:

| DATE | BLUE | GREEN | RED |

| 2014-04-11 | 2 | 1 | 0 |

| 2014-04-10 | 0 | 1 | 1 |

| 2014-04-09 | 0 | 0 | 2 |

I guess I need to group it by date, but the problem is that I cannot "sum" or "count" only BLUE colors etc. Is this even possible? If so, can someone please help me?

Upvotes: 1

Views: 1668

Answers (2)

TechDo
TechDo

Reputation: 18629

Please try:

select 
    "date",
    SUM(case when color='BLUE' then 1 else 0 end) as BLUE,
    SUM(case when color='GREEN' then 1 else 0 end) as GREEN,
    SUM(case when color='RED' then 1 else 0 end) as RED
from
    YourTable
Group by "date"

Upvotes: 3

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

SELECT "DATE",
        SUM(CASE WHEN color='GREEN' THEN 1 ELSE 0 END) as GREEN
        SUM(CASE WHEN color='RED' THEN 1 ELSE 0 END) as RED
        SUM(CASE WHEN color='BLUE' THEN 1 ELSE 0 END) as BLUE
FROM tableName
GROUP BY "DATE"

You need to escape column named DATE with double qouotes as it is a keyword in Oracle

Upvotes: 0

Related Questions