Is there a way to do a SQL sum(case when A=B then 1 else 0) where you get a column for every value that A can take on?

Question

Rather than doing:

select sum(case when a=b then 1 else 0), 
       sum(case when a=c then 1 else 0), 
       sum(case when a=d then 1 else 0)

etc.

I want one statement does this for all values that a can take on.

Would't this do what you are asking, unless I am not understanding correcly?: `select count(1), a from some_table group by a` — sovemp, Jun 08 '16 at 16:16
You're asking for a dynamic pivot. You don't mention your DBMS but this is bound to be a duplicate. — Martin Smith, Jun 08 '16 at 16:19
Hover over the sql tag in the question and read the tool tip. — Dan Bracuk, Jun 08 '16 at 16:29

score 0 · Answer 1 · edited May 23 '17 at 11:58

As Martin Smith's comment states, you are looking for a dynamic pivot. As mentioned this is almost definitely a duplicate, but for your convenience I thought I'd include this reference. This article explains in detail with example data and code how to accomplish what you want. You will likely want to put your aggregation in the CTE like so:

WITH PivotData AS
(
SELECT
a,
COUNT(1)
FROM MyTable
GROUP BY a
)

If you are not used to writing dynamic sql (SET @SQL='[...]'; EXEC(@SQL);) it's usually a good idea to test individual statements before throwing them all inside a string as all formatting and coloring your editor provides will disappear and debugging can be difficult.

EDIT: If you are not using T-SQL the Pivot function will not be available to you. However, it is relatively easy to find references on how to reproduce this behavior in other SQL environments. The answer here, for example, actually explains how to do the full dynamic pivot in MySQL. On a conceptual level the idea is the same, however the syntax may look very different.

1

What if he is not in T-sql? – Andreas Jun 10 '16 at 21:38

score 0 · Answer 2 · answered Jun 10 '16 at 21:42

You really need to tell us what flavor of db you're in. However, in postgres, we have a crosstab(..) function. From the docs:

CREATE TABLE cth(rowid text, rowdt timestamp, attribute text, val text);
INSERT INTO cth VALUES('test1','01 March 2003','temperature','42');
INSERT INTO cth VALUES('test1','01 March 2003','test_result','PASS');
INSERT INTO cth VALUES('test1','01 March 2003','volts','2.6987');
INSERT INTO cth VALUES('test2','02 March 2003','temperature','53');
INSERT INTO cth VALUES('test2','02 March 2003','test_result','FAIL');
INSERT INTO cth VALUES('test2','02 March 2003','test_startdate','01 March 2003');
INSERT INTO cth VALUES('test2','02 March 2003','volts','3.1234');

SELECT * FROM crosstab
(
  'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
  'SELECT DISTINCT attribute FROM cth ORDER BY 1'
)
AS
(
       rowid text,
       rowdt timestamp,
       temperature int4,
       test_result text,
       test_startdate timestamp,
       volts float8
);
 rowid |          rowdt           | temperature | test_result |      test_startdate      | volts
-------+--------------------------+-------------+-------------+--------------------------+--------
 test1 | Sat Mar 01 00:00:00 2003 |          42 | PASS        |                          | 2.6987
 test2 | Sun Mar 02 00:00:00 2003 |          53 | FAIL        | Sat Mar 01 00:00:00 2003 | 3.1234
(2 rows)

Is there a way to do a SQL sum(case when A=B then 1 else 0) where you get a column for every value that A can take on?

2 Answers2