Count by two columns in SQL Server?

Question

Here is an example table:

CREATE TABLE Example 
(
    LastName varchar(255),
    FirstName varchar(255),
    HomeAddress varchar(255),
    City varchar(255) 
);

INSERT INTO Example VALUES ('Murphy', 'James','123 Easy St', 'New York');
INSERT INTO Example VALUES ('Black', 'John','123 Easy St', 'Boston');
INSERT INTO Example VALUES ('Black', 'Amy','123 Easy St', 'Chicago');
INSERT INTO Example VALUES ('Simpson', 'Bill','123 Easy St', 'New York');
INSERT INTO Example VALUES ('Jones', 'James','123 Easy St', 'Chicago');
INSERT INTO Example VALUES ('Black', 'John','123 Easy St', 'Boston');
INSERT INTO Example VALUES ('Murhpy', 'James','123 Easy St', 'New York');

I want to be able to count by two columns, 'LastName' and 'City'. That is - I want this:

Name        New York  Boston  Chicago
-------------------------------------
Jones            0      0       1
Black            0      2       1
Simpson          1      0       0
Murphy           2      0       0

My question is similar to this question. I created the following, which produces very close results:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
        @PivotColumnNames AS NVARCHAR(MAX),
        @PivotSelectColumnNames AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames = ISNULL(@PivotColumnNames + ',','') + QUOTENAME(City)
FROM (SELECT DISTINCT City FROM Example) AS cat

--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames 
= ISNULL(@PivotSelectColumnNames + ',','')
+ 'ISNULL(' + QUOTENAME(City) + ', 0) AS '
+ QUOTENAME(City)
FROM (SELECT DISTINCT City FROM Example) AS cat

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
N'SELECT LastName, ' + @PivotSelectColumnNames + '
FROM Example
pivot(count(City) for City in (' + @PivotColumnNames + ')) as pvt';

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

BUT, this is not quite what I am after. It produces this:

I tried to add 'GROUP BY LastName' to the end of the query, but it does not work. I get an error:

Column 'pvt.Boston' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

score 3 · Accepted Answer · answered Aug 03 '17 at 14:26

You are so close!

Two things:

Use a subquery/derived table to select only the columns you need for your pivot
Don't misspell Murphy

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
N'SELECT LastName, ' + @PivotSelectColumnNames + '
FROM (select LastName, City from Example) e
pivot(count(City) for City in (' + @PivotColumnNames + ')) as pvt';

rextester demo: http://rextester.com/ETJ57985

returns:

+----------+--------+---------+----------+
| LastName | Boston | Chicago | New York |
+----------+--------+---------+----------+
| Black    |      2 |       1 |        0 |
| Jones    |      0 |       1 |        0 |
| Murphy   |      0 |       0 |        2 |
| Simpson  |      0 |       0 |        1 |
+----------+--------+---------+----------+

4

Don't misspell Murphy... LOL! – ATC Aug 03 '17 at 14:29

Kannan Kandasamy · Answer 2 · 2017-08-03T14:30:07.780

1

I think simple pivot will give your query results

Select * from (
    Select LastName, City from Example ) a
pivot (count(city) for city in ([New York],[Boston],[Chicago])) p

If you change your query as below you will get appropriate results:

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
N'Select LastName, ' + @PivotSelectColumnNames + ' from ( SELECT LastName, City
    FROM Example ) a
    pivot(count(City) for City in (' + @PivotColumnNames + ')) as pvt';

Output as below:


+----------+----------+--------+---------+
| LastName | New York | Boston | Chicago |
+----------+----------+--------+---------+
| Black    |        0 |      2 |       1 |
| Jones    |        0 |      0 |       1 |
| Murhpy   |        1 |      0 |       0 |
| Murphy   |        1 |      0 |       0 |
| Simpson  |        1 |      0 |       0 |
+----------+----------+--------+---------+

edited Aug 03 '17 at 14:30

answered Aug 03 '17 at 14:21

Kannan Kandasamy

13,405
3
25
38

That is not dynamic. The example I provided is a simple version of what I am really after, which needs to dynamically query a very large set of possible 'City' and 'LastName' values. Do you know how to edit the query I posted and make it produce the correct results? – lukehawk Aug 03 '17 at 14:23
In you example you have Murphy and Murhphy so it gets two different records – Kannan Kandasamy Aug 03 '17 at 14:23
Updated accordingly – Kannan Kandasamy Aug 03 '17 at 14:24
Ha - Sorry. Good catch. Still got issues with the Jones, though. – lukehawk Aug 03 '17 at 14:27

Caius Jard · Answer 3 · 2017-08-05T04:18:38.337

0

Alternative approach not using PIVOT:

DECLARE @sql NVARCHAR(max);
SET @sql = ''; -- not necessary for CONCAT, but necessary if converting this answer to + style string concatenation 

SELECT 
  @sql = CONCAT(@sql, ', COUNT(CASE WHEN city = ''', City, ''' THEN 1 END) as ', QUOTENAME(City))
FROM
  Example
GROUP BY City

SET @sql = CONCAT('SELECT Name', @sql, ' FROM example GROUP BY name')

EXEC sp_executesql @sql

edited Aug 05 '17 at 04:18

answered Aug 03 '17 at 14:23

Caius Jard

72,509
5
49
80

This is not dynamic. The example I posted is a tiny, simplified version. I am using the approach above to avoid listing hundreds of lines of `COUNT(Case...` – lukehawk Aug 03 '17 at 14:24
Amended, sorry! – Caius Jard Aug 03 '17 at 15:15
Thanks!! I like this better than the PIVOT version, but I am on Server 2008. I think CONCAT was introduced in 2012, and I cannot get that chunk of code to work. You know how to make it 2008-like? – lukehawk Aug 04 '17 at 19:26
This works in 2012, but when I try to change from using CONCAT() to using '+' (to make it work in 2008), the query seems to work, but displays nothing. I just see `Command(s) completed successfully.` – lukehawk Aug 04 '17 at 19:42
That happens when one variable is null. The beauty of CONCAT is that you can `CONCAT('john', null, 'smith')` and get 'johnsmith'. If you try the same with +, anything + a null is null. I'll edit the sql to prevent this, thanks for letting me know.. (edit: added set @sql ='') – Caius Jard Aug 05 '17 at 04:15

Ace Amr · Answer 4 · 2017-08-03T15:10:09.040

0

Try this -

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),
        @PivotColumnNames AS NVARCHAR(MAX),
        @PivotSelectColumnNames AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @PivotColumnNames = ISNULL(@PivotColumnNames + ',','') + QUOTENAME(City)
FROM (SELECT DISTINCT City FROM #example) AS cat

--Get distinct values of the PIVOT Column with isnull
SELECT @PivotSelectColumnNames 
= ISNULL(@PivotSelectColumnNames + ',','')
+ 'ISNULL(' + QUOTENAME(City) + ', 0) AS '
+ QUOTENAME(City)
FROM (SELECT DISTINCT City FROM #example) AS cat

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
N'SELECT LastName, ' + @PivotSelectColumnNames + '
FROM #example
pivot(count(City) for City in (' + @PivotColumnNames + ')) as pvt group by LastName, ' + @PivotColumnNames;

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery

edited Aug 03 '17 at 15:10

answered Aug 03 '17 at 14:37

Ace Amr

108
5

Edit it, select all the text and press the {} button above the edit area – Caius Jard Aug 03 '17 at 15:02
Thanks Caius. I've learnt something new on this forum today :-) – Ace Amr Aug 03 '17 at 15:10
Another tip, then.. and it's really obscure. If you ever upload an imag eusing SO's image upload, it will up it to imgur, and place a link at the end of your editbox with a [1] like a wikipedia reference, and then put `[enter image descripton][1]` in your post. If you add a leading `!` to it like `![image of excel grid][1]` then it will show the image inline rather than as a link.. So if your image uploads look like links, not thumbnails, put a `!`. Took me ages to figure that one out – Caius Jard Aug 03 '17 at 15:19

Count by two columns in SQL Server?

4 Answers4