getting sum of column and not delete some records

user_6001531 0
Viewed 59

here are some data from the tables

slm

id grade_level period
1 Grade 1 1st Grading Period
2 Grade 2 2nd Grading Period

slm_activities

id slmid activity count
1 1 77 5
2 2 72 6

period

id period
1 1st Grading Period
2 2nd Grading Period
3 3rd Grading Period
4 4th Grading Period

activities

id activity
72 lesson
75 page
77 quiz

i need to get this kind of output for mysql it will display all kinds of activities and for all periods then sum their count value, activitycount will display 0 if it doesn't encounter other activities for that period

activitycount activity period
5 quiz 1st Grading Period
0 quiz 2nd Grading Period
0 quiz 3rd Grading Period
0 quiz 4th Grading Period
0 lesson 1st Grading Period
6 lesson 2nd Grading Period
0 lesson 3rd Grading Period
0 page 1st Grading Period
0 page 2nd Grading Period
0 page 3rd Grading Period
0 page 4th Grading Period

this is my current mysql query

SELECT SUM(count) AS activitycount, activities.activity, periods.period
FROM slm_activities 
LEFT JOIN activities ON activities.id = slm_activities.activity 
LEFT JOIN slm ON slm_activities.slmid = slm.id 
LEFT JOIN periods ON periods.period = slm.period
GROUP BY activities.activity, periods.period
join mysql sql
1 Answers

Since you need to show all periods for each activities, you need to use CROSS JOIN for this. Also the table slm should store the id of periods table instead of period field.

SELECT SUM(IFNULL(count, 0)) AS activitycount, activities.activity, periods.period
FROM activities
CROSS JOIN periods
LEFT JOIN slm ON periods.period = slm.period
LEFT JOIN slm_activities ON activities.id = slm_activities.activity
 AND slm.id = slm_activities.slmid
GROUP BY activities.activity, periods.period
ORDER BY activities.activity, periods.period;

Result

activitycount activity period
0 lesson 1st Grading Period
6 lesson 2nd Grading Period
0 lesson 3rd Grading Period
0 lesson 4th Grading Period
0 page 1st Grading Period
0 page 2nd Grading Period
0 page 3rd Grading Period
0 page 4th Grading Period
5 quiz 1st Grading Period
0 quiz 2nd Grading Period
0 quiz 3rd Grading Period
0 quiz 4th Grading Period

See this fiddle.

user_13692350
Related