select count problem 2

[snuv]

I have two queries that I want to combine.

select Count(flag) as Good
from table
where 
flag = 'G'
Group by Fruit

select Count(flag) as Bad
from table
where 
flag = 'B'
Group by Fruit

Is it possible to do this in one query?

Cheers
Snuv

"If it could have gone wrong earlier and it didn't, it ultimately would have been beneficial for it to have." : Murphy's Ultimate Corollary

 

[psymonj]

As you're likely to want to know which is which, this query looks pretty good to me.

else, get them all by:
SELECT * FROM table WHERE flag REGEXP 'G|B' GROUP BY fruit

Then you can display to a table, export to a feed or whatever you kids do with queries these days

Simon Clements-Hawes

http://www.silvercoast.co.uk

 

[r937]

simon, never mix the dreaded, evil "select star" with GROUP BY

snuv, here you go --

SELECT Fruit
     , flag
     , Count(*) AS flagcount
  FROM table
 WHERE flag IN ( 'G' , 'B' )
GROUP
    BY Fruit
     , flag

if 'G' and 'B' are the only possible values for flag, then you don't need the WHERE clause


r937.com | rudy.ca

 

[snuv]

Thanks for this

I'm trying to get two columns of values
the count of goods grouped by fruit
and the count of bads grouped by fruit
(There can be other values in flag)

eg

apple G
Apple G
Apple B
Plum G
Plum B
Orange X


to give
G B
Apple 2 1
Plum 1 1
Orange 0 0




Cheers
Snuv





"If it could have gone wrong earlier and it didn't, it ultimately would have been beneficial for it to have." : Murphy's Ultimate Corollary

 

[r937]

SELECT Fruit
     , SUM(CASE WHEN flag = 'G'
                THEN 1 ELSE 0 END) AS G
     , SUM(CASE WHEN flag = 'B'
                THEN 1 ELSE 0 END) AS B
  FROM table
GROUP
    BY Fruit

r937.com | rudy.ca

 

[snuv]

r937 - Thanks for that

"If it could have gone wrong earlier and it didn't, it ultimately would have been beneficial for it to have." : Murphy's Ultimate Corollary