CODEWITHSUNDEEP
mysqlsqlselectcountgroup-by
vivek
vivek

Reputation: 329

Group by two column and use the distinct values as column name in MYSQL

Basically i need the distinct values from a column to be as column name via Query

Example is below

  id status  userId  date
  ------------------------
  1   p      u12  01/01/2014
  2   p      u13  01/01/2014
  3   a      u14  01/01/2014
  4   hd     u15  01/01/2014
  5   a      u16  01/01/2014
  6   p      u12  01/02/2014
  7   a      u13  01/02/2014
  8   p      u14  01/02/2014

output

 date        p  a  hd
 ------------------------
 01/01/2014  2  2   1
 02/01/2014  2  1   0

The status 'p','a','hd' are used as column name and it is grouped by date

Upvotes: 3

Views: 1344

Answers (3)

Abdur Rahim
Abdur Rahim

Reputation: 4031

This SQL is tested

    select p.date1,P.p,A.a,case when HD.hd is null then 0 else HD.hd end hd from (select COUNT(status1) p,date1 from tbl1 where status1 ='p' 
group by date1) as P LEFT JOIN (select COUNT(status1) a,date1 from tbl1 where status1 ='a' 
group by date1) as A on P.date1 = A.date1 LEFT JOIN (select COUNT(status1) hd,date1 from tbl1 where status1 ='hd' 
group by date1) as HD on A.date1 = HD.date1

just change table name or column name as you wish.

Upvotes: 0

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT a.date, SUM(a.status='p') AS p, SUM(a.status='a') AS a, 
       SUM(a.status='hd') AS hd
FROM tableA a
GROUP BY a.date

Check this SQL FIDDLE DEMO

OUTPUT

|                           DATE | P | A | HD |
|--------------------------------|---|---|----|
| January, 01 2014 00:00:00+0000 | 2 | 2 |  1 |
| January, 02 2014 00:00:00+0000 | 2 | 1 |  0 |

Upvotes: 2

Devart
Devart

Reputation: 122002

Try this query, but it forces you to know all possible statuses (result field names) -

SELECT
  date,
  COUNT(IF(status = 'p', status, NULL)) AS p,
  COUNT(IF(status = 'a', status, NULL)) AS a,
  COUNT(IF(status = 'hd', status, NULL)) AS hd
FROM
  table
GROUP BY
  date;

You also can try to automate this logic, have a look at this link - Dynamic pivot tables (transform rows to columns).

Upvotes: 0

Related Questions