CODEWITHSUNDEEP
sql-serverselect
crashtestxxx
crashtestxxx

Reputation: 1445

Multiple COUNT SELECTS from the same table in one query

For some people it might look very simple, but I just simply can't get it.
I have multiple MS-SQL SELECT queries from the same table over and over:

SELECT count(Page) as tAEC
FROM someTable
WHERE Page LIKE '%AEC%'

next one

SELECT count(Page) as tTOL
FROM someTable
WHERE Page LIKE '%TOL%'

and so on...

What would be more efficient way to write this query. I Googled bunch of similar questions but I couldnt make any of them to work. So any help is greatly appreciated.

Upvotes: 30

Views: 60504

Answers (2)

Pierre de LESPINAY
Pierre de LESPINAY

Reputation: 46228

You can use a GROUP BY Page

SELECT
  Page
, COUNT(Page) as "nb"
FROM someTable
GROUP BY Page

You can also do a GROUP BY CASE...

SELECT
  CASE
    WHEN Page LIKE '%AEC%' THEN "AEC"
    WHEN Page LIKE '%TOL%' THEN "TOL"
    ELSE ""
  END AS "type"
, count(Page) as "nb"
FROM someTable
GROUP BY type

You can also COUNT IF

SELECT
  COUNT(IF(Page LIKE '%AEC%', 1, NULL) THEN "AEC"
, COUNT(IF(Page LIKE '%TOL%', 1, NULL) THEN "TOL"
FROM someTable

Upvotes: 7

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171579

SELECT sum(case when Page LIKE '%AEC%' then 1 end) as tAEC, 
    sum(case when Page LIKE '%TOL%' then 1 end) as tTOL 
FROM someTable

Upvotes: 91

Related Questions