SQL multiple COUNT

Question

Let's consider this table:

[name] [type]
"Ken Anderson" 1
"John Smith" 2
"Bill Anderson" 1
"George Anderson" 1
"Taylor Smith" 1
"Andrew Anderson" 2
"Dominic Smith" 2

and that query:

SELECT mates.type, COUNT(*) AS SmithsCount
FROM mates
WHERE mates.name LIKE "* Smith"
GROUP BY mates.type

The result should be like

[type] [SmithsCount]
1 1
2 2

What if I want to get also Andersons Count in each group? Like

[type] [SmithsCount] [AndersonsCount]
1 1 3
2 2 1

And, of course, I want this to be most simple as it can be ;) I'm pretty new in SQL, I readed tutorials on W3 Schools and http://www.sql-tutorial.net/ but there are just poorly exampled basics, any "more" complicated queries. Anybody has some useful links? Thanks.

What you have is a bad design not a SQL problem. NEVER store first and last name in one field and then try to query on last name. YOu cannot use the indexes when you have a wildcard as the first character. Learn correctly not to ever do this before you learn workaround queries. — HLGEM, Mar 14 '11 at 17:05
Yep, I know that. That was just an example, not the real problem. — user659160, Mar 14 '11 at 17:14

score 6 · Accepted Answer · answered Mar 14 '11 at 16:49

6

select type,
       sum(case when name like '% Smith' then 1 else 0 end) as SmithCount,
       sum(case when name like '% Anderson' then 1 else 0 end) as AndersonCount
    from mates
    group by type

answered Mar 14 '11 at 16:49

Joe Stefanelli

132,803
19
237
235

I'm using MS Access (not my idea...) however this pointet me to "iif" statement which works the same way. Thanks. – user659160 Mar 14 '11 at 17:12

score 1 · Answer 2 · edited May 23 '17 at 11:47

1

You need a pivot table. This is a feature supported by some RDBMS (Oracle, SQLServer and probably others).

A pivot table let's you use values as columns for aggregations. See my post here: How to transform vertical data into horizontal data with SQL?

The pivot table will alow you to also get the counts of all the other people in your list.

edited May 23 '17 at 11:47

Community

1
1

answered Mar 14 '11 at 16:55

Falcon

3,150
2
24
35

Well, to the noob who downvoted me: What he needs is a pivot table and he should be introduced to the concept. – Falcon Mar 14 '11 at 16:58
Joe's solution is pretty clear for me but that pivot table could be useful for other things. I'll read about it, thanks. – user659160 Mar 14 '11 at 17:15

score 0 · Answer 3 · answered Mar 14 '11 at 16:51

Your query is close, but you must use % instead of * as the wildcard character.

select type,
    sum(case when name like '%Smith' then 1 else 0 end) as SmithCount,
    sum(case when name like '%Anderson' then 1 else 0 end) as AndersonCount
group by type

score 0 · Answer 4 · answered Mar 14 '11 at 17:01

In standard SQL parlance this is not supported in the presentation that you propose.

Standard SQL way would be to first normalize data into mates.first_name, mates.last_name and then do:

SELECT mates.type, mates.last_name, COUNT(*) AS last_name_count
FROM mates
WHERE mates.last_name IN ('Smith', 'Anderson')
GROUP BY mates.type, mates.last_name

Which would provide output such as

type last_name last_name_count
1    Anderson  3
1    Smith     1
2    Anderson  1
2    Smith     2

this is the same info that you are looking for, but the format/presentation is not the same. Historically you were supposed to pivot/crosstab this data in the client application (as part of the presentation layer).

Of course a lot of times it is useful or necessary to do it in SQL layer so extensions to the standard were made, such as pivot (MSSQL) or crosstab (postgres), etc...

SQL multiple COUNT

4 Answers4