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

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

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
Asked:
dba123
1 Solution
 
bwdowhanCommented:
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
 
dba123Author Commented:
I want 2 separate counts then shown the results in the same row.  Probably have to do 2 selects then join
0
 
LowfatspreadCommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Scott PletcherSenior 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
 
LowfatspreadCommented:
>>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
 
dba123Author Commented:
thanks Scott & lowfat
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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