[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 300

# 2 COUNT in same select statement

Is it possible to peform a count on both fields withought having to create 2 separate selects that are basically the same?

SELECT   COUNT(m.somefield)
FROM         mytable m INNER JOIN
table2 t ON t.TheID = m.TheID
WHERE m.somefield IN ('BA01')

I want

SELECT   COUNT of BA, Count of CT
FROM         mytable m INNER JOIN
table2 t ON t.TheID = m.TheID
WHERE m.somefield IN ('BA', 'CT')
0
dba123
1 Solution

Commented:
Try:

SELECT m.Somefield, count(*) as Total
FROM         mytable m INNER JOIN
table2 t ON t.TheID = m.TheID
WHERE m.somefield IN ('BA', 'CT')
GROUP BY m.Somefield
0

Author Commented:
I want 2 separate counts then shown the results in the same row.  Probably have to do 2 selects then join
0

Commented:
SELECT   sum(case somefield when 'BA' then 1 else null end) as BAcount
,sum(case somefield when 'CT' then 1 else null end) as CTcount

FROM    mytable m
INNER JOIN  table2 t ON t.TheID = m.TheID
WHERE m.somefield IN ('BA', 'CT')

0

Senior DBACommented:
More exactly:

SELECT SUM(CASE WHEN m.somefield IN ('BA', 'CT') THEN 1 ELSE 0 END) AS [BA|CT],
SUM(CASE WHEN m.somefield IN ('BA01) THEN 1 ELSE 0 END) AS [BA01]
FROM         mytable m INNER JOIN
table2 t ON t.TheID = m.TheID
WHERE m.somefield IN ('BA', 'CT', 'BA01')

Btw, do you really need table2 in this query?  Unless you need to verify that "TheID" exists in the other table, you can remove the reference to table2.
0

Commented:
>>Btw, do you really need table2 in this query?  Unless you need to verify that "TheID" exists in the other table, you can remove the reference to table2.

Scott

there are several reasons why the Join to the other table could be integral to the count process

e.g. 1:M relationship   , existence check

0

Author Commented:
thanks Scott & lowfat
0

## Featured Post

Tackle projects and never again get stuck behind a technical roadblock.