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

Question

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

Abhik Chakraborty · Accepted Answer · 2015-06-26 08:39:22Z

4

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

answered Jun 26, 2015 at 8:39

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Koray Over a year ago

This query returns what i need. Thanks for your quick response!

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related