CODEWITHSUNDEEP
mysqlsqldate
sshah98
sshah98

Reputation: 352

SQL pivot column names and organize by date

I have a table as such: 

date       | page
-----      |-----
2018-01-01 | good
2018-01-01 | good
2018-01-01 | good
2018-01-01 | bad
2018-01-02 | good

How do I organize by the values in the page column by date as such:

date       | good | bad
-----      |------|----
2018-01-01 | 3    | 1
2018-01-02 | 1    | 0

Upvotes: 0

Views: 80

Answers (4)

Ranjith
Ranjith

Reputation: 153

Using pivot table as,

 create table #pivot(date date,page varchar(20))
 insert into #pivot values
 ('2018-01-01','good')
 ,('2018-01-01','good')
 ,('2018-01-01','good')
 ,('2018-01-01','bad')
 ,('2018-01-02','good')

 select date,good,bad from(  
 select date, page from #pivot 
 ) f
 pivot
( count(page) for page in (good,bad)
) p

Upvotes: 1

Jacob Green
Jacob Green

Reputation: 3

I'm not sure what platform you are using but if it is in Oracle this will do the same.

select date, sum(decode(page,'good',1,0)) as good, sum(decode(page,'bad',1,0)) as bad              
from table t                                                                       
group by date;

Upvotes: 0

Sudhanshu Jain
Sudhanshu Jain

Reputation: 534

This can work just add a case inside sum function:

select date, 
       sum( case when page = 'good' then 1 else 0 end) as good, 
       sum( case when page = 'bad' then 1 else 0 end ) as bad 
from table t 
group by date

Upvotes: 1

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You need conditional aggregation :

select date, 
       sum(case when page = 'good' then 1 else 0 end) as good, 
       sum(case when page = 'bad' then 1 else 0 end) as bad
from table t
group by date;

However, MySQL has shorthand for this :

select date, 
       sum( page = 'good') as good, 
       sum( page = 'bad' ) as bad
from table t
group by date;

Upvotes: 1

Related Questions