Thursday, 19 September 2013

MySQL - One SQL request instead two (both request include diffrent COUNT(*)

MySQL - One SQL request instead two (both request include diffrent COUNT(*)

Once again i need yours help ;). I have a lot data and mysql request are
slower and slower so the need request that i need i want group in one
comand.
My example DB structure:
|product|opinion (pos/neg)|reason|
__________________________________
|milk | pos | good |
|milk | pos |fresh |
|chocolate| neg | old |
|milk | neg | from cow|
So i need information about all diffrent product (GROUP BY) count of it,
and count of pos opinion for each product. I want output like that:
|product|count|pos count|
_________________________
|milk | 3 | 2 |
|chocolate| 1 | 0 |
I hope that my explain was good enought ;) Or go to work: I write two
commands
SELECT COUNT(*) as pos count FROM table WHERE product = "milk" AND opinion
= "pos" GROUP BY `opinion`
And Second one
SELECT product, COUNT(*) FROM table GROUP BY `product`
I don't know how to join this two request, maybe that is impossible? In my
real use code i have additional category collumn and i use WHERE in second
command too

No comments:

Post a Comment