asked on

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')

bwdowhan

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

dba123

ASKER

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

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Scott Pletcher

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.

Lowfatspread

>>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

dba123

ASKER

thanks Scott & lowfat