CODEWITHSUNDEEP
mysqlsqlselectcount
Koray
Koray

Reputation: 407

How to get multiple counts from a table with a single SELECT statement

This query looks like have logical error so doesnt give what i want exactly. I hope i explained clearly below. Could you help me please?

    SELECT a.CreateBy, CreateDate,
      (SELECT COUNT(*) FROM MyTable WHERE Item1=1) as Item1Count,
      (SELECT COUNT(*) FROM MyTable WHERE Item1=2) as Item2Count,
      (SELECT COUNT(*) FROM MyTable WHERE Item1=3) as Item3Count
    FROM MyTable a;

MyTable

Id   | CreateBy | CreateDate  | Item1 | Item2 | Item3
-----------------------------------------------------
100  | John     | 01.06.2015  |  1    |   0   |   1
101  | John     | 01.06.2015  |  1    |   1   |   1
102  | Ahn      | 01.06.2015  |  0    |   1   |   0
103  | Patrick  | 01.06.2015  |  1    |   1   |   0
104  | John     | 02.06.2015  |  1    |   0   |   1

I want to get data like below.

CreateBy | CreateDate | Item1Count | Item2Count | Item3Count
------------------------------------------------------------
John     | 01.06.2015 |     2     |      1      |    2
John     | 02.06.2015 |     1     |      0      |    1
Patrick  | 01.06.2015 |     1     |      1      |    0
Ahn      | 01.06.2015 |     0     |      1      |    0

Upvotes: 1

Views: 48

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can use conditional sum and then group by something as

select 
CreateBy,
CreateDate,
sum(Item1=1) as Item1Count,
sum(Item2=1) as Item2Count,
sum(Item3=1) as Item3Count
from MyTable
group by CreateBy,CreateDate

Upvotes: 4

Related Questions