Geek asks and answers

SQL multiple COUNT

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 this request:

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

The result should look like

[type] [SmithsCount]
1 1
2 2

What if I want to get also the number of andersons in each group? how

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

And, of course, I want this to be the easiest, as it may be;) I am new to SQL, I read tutorials on W3 Schools and http://www.sql-tutorial.net/ , but there are only poorly studied basics, any "more complex" queries. Does anyone have any useful links? Thank.

+3
source share
4 answers
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
+6
source

You need a pivot table. This is a feature supported by some DBMSs (Oracle, SQLServer, and possibly others). A.

. . : SQL?

.

+1

, % * .

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
0

SQL , .

SQL - mates.first_name, mates.last_name, :

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

,

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

, , / . /- ( ).

Of course, many times it is useful or necessary to do this at the SQL level, so extensions to the standard, such as pivot (MSSQL) or crosstab (postgres), etc. were added .

0
source

More articles:


All Articles