CODEWITHSUNDEEP
sqlsql-server
Tony
Tony

Reputation: 3409

SQL Select for multiple where clause

I am trying to create SQL Select that returns counts of a certain field based on a field. So, here is what I am trying to do.

Select count(distinct id) as TotalCount, -- this will be the total of id
count(distinct id where type='A') as TotalA, -- this will be total when type='A'
count(distinct id where type='B') as TotalB -- This will be total when type = 'B'
from MyTable

Basically, TotalCount = TotalA + TotalB.

How can I achieve this in SQL Select Statement? Thanks.

Upvotes: 1

Views: 1188

Answers (3)

Tim
Tim

Reputation: 5421

Why not simply UNION the separate queries.

  Select 'all' as which, count(distinct id) as Total from mytable
  union 
  select 'a' as which, count(distinct id) where type='A' as Total from mytable
  union
  select 'b' as which, count(distinct id) where type='B' as Total from mytable

Upvotes: 0

Julio Guerra
Julio Guerra

Reputation: 5661

You can do it like that:

SELECT
  count(distinct id) as TotalCount,
  sum(CASE WHEN type = 'A' THEN 1 ELSE 0) as TotalA,
  sum(CASE WHEN type = 'B' THEN 1 ELSE 0) as TotalB,
FROM
  MyTable

Count per type:

SELECT
  type,
  count(DISTINCT id)
FROM
  MyTable
GROUP BY
  type

Upvotes: 1

Remus Rusanu
Remus Rusanu

Reputation: 294437

Select count(distinct id) as TotalCount, -- this will be the total of id
count(distinct case type when 'A' then id else NULL end) as TotalA,
count(distinct case type when 'B' then id else NULL end) as TotalB 
from MyTable;

Of course TotalCount may or may not be TotalA + TotalB, depending on the actual data.

Upvotes: 5

Related Questions