I have a simple database where I've got 2 columns "product" and "flag" where I want to count occurrences. "product" contains random product names and "flag" will have an "x" if flagged or anything except and "x" if not flagged.I currently use two queries as follows:SELECT product, COUNT(product) AS flags FROM `TABLE` WHERE flag = 'x' GROUP BY product ORDER BY flags DESC
which gives a table:| product | flags |-------------------| pear | 6 || apple | 4 || orange | 1 |-------------------
SELECT product, COUNT(product) AS noflags FROM `TABLE` WHERE flag != 'x' GROUP BY product ORDER BY noflags DESC
which gives a table:| product | noflags |---------------------| apple | 4 || pear | 3 || orange | 0 |---------------------
I want to combine these two queries so I am effectively counting occurrences twice to produce a table as follows:| product | flags | no flags |------------------------------| pear | 6 | 3 || apple | 4 | 4 || orange | 1 | 0 |------------------------------
Any help much appreciated.
Using count twice in one query?
Posted - 2008-07-16 : 08:14:04
