table structure image score breakdowni have a table of golf scores called scores. column headings are golfer, golf_course, h1, h2, h3 and so on to h18. I have a form that adds scores in to each column on the basis of 0 = no score, 1 = bogey, 2 = par, 3 = birdie, 4 = eagle. what I am trying to achieve is a statement that can return the number of no scores, bogeys, pars, birdies or eagles for each row. any help I would be very grateful for.
SELECT golfer,
course,
(SUM(CASE WHEN scores.h1 = 0 THEN 1
WHEN scores.h2 = 0 THEN 1
WHEN scores.h3 = 0 THEN 1
WHEN scores.h4 = 0 THEN 1
WHEN scores.h5 = 0 THEN 1
WHEN scores.h6 = 0 THEN 1
WHEN scores.h7 = 0 THEN 1
WHEN scores.h8 = 0 THEN 1
WHEN scores.h9 = 0 THEN 1
WHEN scores.h10 = 0 THEN 1
WHEN scores.h11 = 0 THEN 1
WHEN scores.h12 = 0 THEN 1
WHEN scores.h13 = 0 THEN 1
WHEN scores.h14 = 0 THEN 1
WHEN scores.h15 = 0 THEN 1
WHEN scores.h16 = 0 THEN 1
WHEN scores.h17 = 0 THEN 1
WHEN scores.h18 = 0 THEN 1 ELSE 0
END)) AS total_no_score,
(SUM(CASE WHEN scores.h1 = 1 THEN 1
WHEN scores.h2 = 1 THEN 1
WHEN scores.h3 = 1 THEN 1
WHEN scores.h4 = 1 THEN 1
WHEN scores.h5 = 1 THEN 1
WHEN scores.h6 = 1 THEN 1
WHEN scores.h7 = 1 THEN 1
WHEN scores.h8 = 1 THEN 1
WHEN scores.h9 = 1 THEN 1
WHEN scores.h10 = 1 THEN 1
WHEN scores.h11 = 1 THEN 1
WHEN scores.h12 = 1 THEN 1
WHEN scores.h13 = 1 THEN 1
WHEN scores.h14 = 1 THEN 1
WHEN scores.h15 = 1 THEN 1
WHEN scores.h16 = 1 THEN 1
WHEN scores.h17 = 1 THEN 1
WHEN scores.h18 = 1 THEN 1 ELSE 0
END)) AS total_bogey,
(SUM(CASE WHEN scores.h1 = 2 THEN 1
WHEN scores.h2 = 2 THEN 1
WHEN scores.h3 = 2 THEN 1
WHEN scores.h4 = 2 THEN 1
WHEN scores.h5 = 2 THEN 1
WHEN scores.h6 = 2 THEN 1
WHEN scores.h7 = 2 THEN 1
WHEN scores.h8 = 2 THEN 1
WHEN scores.h9 = 2 THEN 1
WHEN scores.h10 = 2 THEN 1
WHEN scores.h11 = 2 THEN 1
WHEN scores.h12 = 2 THEN 1
WHEN scores.h13 = 2 THEN 1
WHEN scores.h14 = 2 THEN 1
WHEN scores.h15 = 2 THEN 1
WHEN scores.h16 = 2 THEN 1
WHEN scores.h17 = 2 THEN 1
WHEN scores.h18 = 2 THEN 1 ELSE 0
END)) AS total_par,
(SUM(CASE WHEN scores.h1 = 3 THEN 1
WHEN scores.h2 = 3 THEN 1
WHEN scores.h3 = 3 THEN 1
WHEN scores.h4 = 3 THEN 1
WHEN scores.h5 = 3 THEN 1
WHEN scores.h6 = 3 THEN 1
WHEN scores.h7 = 3 THEN 1
WHEN scores.h8 = 3 THEN 1
WHEN scores.h9 = 3 THEN 1
WHEN scores.h10 = 3 THEN 1
WHEN scores.h11 = 3 THEN 1
WHEN scores.h12 = 3 THEN 1
WHEN scores.h13 = 3 THEN 1
WHEN scores.h14 = 3 THEN 1
WHEN scores.h15 = 3 THEN 1
WHEN scores.h16 = 3 THEN 1
WHEN scores.h17 = 3 THEN 1
WHEN scores.h18 = 3 THEN 1 ELSE 0
END)) AS total_birdie,
(SUM(CASE WHEN scores.h1 = 4 THEN 1
WHEN scores.h2 = 4 THEN 1
WHEN scores.h3 = 4 THEN 1
WHEN scores.h4 = 4 THEN 1
WHEN scores.h5 = 4 THEN 1
WHEN scores.h6 = 4 THEN 1
WHEN scores.h7 = 4 THEN 1
WHEN scores.h8 = 4 THEN 1
WHEN scores.h9 = 4 THEN 1
WHEN scores.h10 = 4 THEN 1
WHEN scores.h11 = 4 THEN 1
WHEN scores.h12 = 4 THEN 1
WHEN scores.h13 = 4 THEN 1
WHEN scores.h14 = 4 THEN 1
WHEN scores.h15 = 4 THEN 1
WHEN scores.h16 = 4 THEN 1
WHEN scores.h17 = 4 THEN 1
WHEN scores.h18 = 4 THEN 1 ELSE 0
END)) AS total_eagle,
FROM scores
This may work.
Hope this helps.
Thanks Subin C Poonamgode,
you got me on the right track. I tweaked your solution to make the following code work
SELECT `golfer`,`course_name`,
(CASE 0 WHEN `h1` THEN 1 ELSE 0 END
+CASE 0 WHEN `h2` THEN 1 ELSE 0 END
+CASE 0 WHEN `h3` THEN 1 ELSE 0 END
+CASE 0 WHEN `h4` THEN 1 ELSE 0 END
+CASE 0 WHEN `h5` THEN 1 ELSE 0 END
+CASE 0 WHEN `h6` THEN 1 ELSE 0 END
+CASE 0 WHEN `h7` THEN 1 ELSE 0 END
+CASE 0 WHEN `h8` THEN 1 ELSE 0 END
+CASE 0 WHEN `h9` THEN 1 ELSE 0 END
+CASE 0 WHEN `h10` THEN 1 ELSE 0 END
+CASE 0 WHEN `h11` THEN 1 ELSE 0 END
+CASE 0 WHEN `h12` THEN 1 ELSE 0 END
+CASE 0 WHEN `h13` THEN 1 ELSE 0 END
+CASE 0 WHEN `h14` THEN 1 ELSE 0 END
+CASE 0 WHEN `h15` THEN 1 ELSE 0 END
+CASE 0 WHEN `h16` THEN 1 ELSE 0 END
+CASE 0 WHEN `h17` THEN 1 ELSE 0 END
+CASE 0 WHEN `h18` THEN 1 ELSE 0 END)
AS Blobby
FROM scores
To get all the other condition I need I just changed the CASE 0 to 1, 2, 3 and 4. Thanks again :-)