0

I'm using this query:

SELECT SUM(voc=1) AS s, SUM(voc=2) AS d, SUM(voc=3) AS p FROM `pl`;

But now I need to modify this query, so it'll SUM when voc is equal to the multiple numbers:

SELECT SUM ( voc = 1 OR voc = 7 OR voc = 3 ) as `s` FROM `pl`;

Unfortunately , the above is not working properly, I've read that this can be done using CASE, but my knowledge about that is too small...

Cyclone
  • 14,839
  • 23
  • 82
  • 114

1 Answers1

3
SELECT SUM ( CASE WHEN voc = 1 OR voc = 7 OR voc = 3 THEN 1 ELSE 0 END) as `s` FROM `pl`;

UPD As stated by @Raul in comment - WHERE should be much more effective, like this:

SELECT COUNT(*) AS `s` FROM `p1` WHERE voc IN (1,3,7);

UPD2 Changed Sum's to Count's - thanks to @newtower

Sergey Kudriavtsev
  • 10,328
  • 4
  • 43
  • 68
  • 1
    `SUM(voc) where voc=3` is not the same as `SUM(voc=3)`. In both cases `COUNT` would be better. – newtover Jan 15 '12 at 13:43
  • I need to get data count on how many fields with `voc=xx` exist in the table. Your queries don't help me since I need to select multiple voc, see the example: `SELECT SUM(OR)COUNT(voc = 1 OR 7 OR 3) AS 's', SUM(OR)COUNT(voc = 2 OR 8 OR 4) from pl;` (OR) means SUM or COUNT because I don't know which one will do this job. – Cyclone Jan 15 '12 at 13:44
  • @SergeyKudriavtsev Have you seen my comment? – Cyclone Jan 15 '12 at 13:52
  • @Cyclone: If you need to select on multiple voc then you may use my first query by slightly modifying it: `SELECT SUM(CASE WHEN voc = 1 OR voc = 7 OR voc = 3 THEN 1 ELSE 0 END) AS 's', SUM(CASE WHEN voc = 2 OR voc = 8 OR voc = 4 THEN 1 ELSE 0 END) from pl;` – Sergey Kudriavtsev Jan 15 '12 at 13:55
  • @SergeyKudriavtsev Thank you Sergey, now I got it :) – Cyclone Jan 15 '12 at 13:57