# Count two distinct items in a table.

• July 16th, 04:13 PM
David
Count two distinct items in a table.
I have a table with roughly the following values:
(each restarant will have such a table)

======== ==========
Fish & Chips NO
Beans YES
Cake YES
Beef NO
Corn YES
Steak NO

I need to compose a quesry that will give me a distinct count for all
the Vegetarian stuff, and all the non-vegi stuff. (based on above
example)

Vegi = 4
Non-Vegi=3

I can figure that part out... My problem is, i need to determine if
the vegetarian items outnumber the non-vegetarian items. Any ideas how
I can determine this is a query?

Thanks
David
• July 16th, 04:25 PM
Anith
Re: Count two distinct items in a table.
If you want it as a 'vertical' resultset :

SELECT Vegetarian, COUNT(Vegetarian)
FROM tbl
GROUP BY Vegetarian ;

You can get it in a single row like:

SELECT SUM(CASE Vegetarian WHEN 'YES' THEN 1 ELSE 0 END) AS "Vegi",
SUM(CASE Vegetarian WHEN 'NO' THEN 1 ELSE 0 END) AS "Non-Vegi"
FROM tbl ;

--
- Anith

• July 16th, 04:28 PM
Re: Count two distinct items in a table.
Try,

select case when NoVegCount > VegCount Then 'Non-Veg'
when NoVegCount < VegCount Then 'Veg'
else 'Both Veg'
end
from
(select sum(case when Vegetarian = 'No' then 1 end) NoVegCount,
sum(case when Vegetarian = 'Yes' then 1 end) VegCount
from Test7) VegCount

HTH

"David" <com> wrote in message

• July 16th, 04:57 PM
Martin
Re: Count two distinct items in a table.
Try:

select Vegetarian, Count(*) from theTable
group by Vegetarian

The reason why this works is because count() operates on the group, not the
entire result set. You could get fancier and use a CASE statement to
substitue "Vegetarian" for 'YES' and 'Non-Veggie' for 'NO'

regards, -marty nicholson

"David" <com> wrote in message