CODEWITHSUNDEEP
mysqldatabase
Fuxi
Fuxi

Reputation: 7599

mysql query: how to

here's my (simplified) table structure:

table: category_main
id  name
-------------
1   food
2   vegetable

table category_sub
id  id_catmain  name 
---------------------
10  1           cake
11  1           chocolate 
12  1           burger
13  2           apple
14  2           banana

table images
id  id_catsub filename         views
-------------------------------------
 1  10        cake1.jpg        11
 2  10        cake2.jpg        24 
 3  10        cake3.jpg        65
 4  11        chocolate1.jpg   31
 5  11        chocolate2.jpg   62
 6  11        chocolate3.jpg   32
 7  11        chocolate4.jpg   58
 8  12        burger1.jpg      23
 9  12        burger2.jpg      43 
10  12        burger3.jpg      76
11  13        apple1.jpg       29
11  13        apple2.jpg       67
11  14        banana1.jpg      78

desired output:

id  name        total_views
----------------------------
1   food        425 
2   vegetable   174

as you can see i want to get the total views for each main category. currently i'm running a loop for each subcategory but there must be an easier and faster way :/

thanks

Upvotes: 1

Views: 38

Answers (3)

Nir Levy
Nir Levy

Reputation: 12953

simply join the three tables, and then you can sum the views grouped by the id's: 

select cm.id, cm.name, sum(i.views) as total_views from
category_main as cm inner join category_sub as cs on cm.id = cs.id_catmain
inner join cs.id = i.id_catsub group by cm.id

Upvotes: 1

Jakub Matczak
Jakub Matczak

Reputation: 15676

Double LEFT JOIN + aggregation will do the job.

SELECT cm.id, cm.name, sum(images.views) as views
FROM category_main as cm
LEFT JOIN category_sub as cs ON cs.id_catmain = cm.id
LEFT JOIN images ON images.id_carsub = cs.id
GROUP BY cm.id
ORDER BY views DESC;

LEFT JOIN (instead of JOIN) will make you sure that you have all categories listed even if there's no subcategory or image in it. If you don't want empty categories to be listed, then use JOIN.

Upvotes: 3

pBuch
pBuch

Reputation: 1001

SELECT c.id AS id, c.name AS name, sum(i.views) AS total_views
FROM category_main c, category_sub s, images i
WHERE c.id=s.id_catmain and s.id=i.id_catsub
GROUP BY c.id,c.name;

Upvotes: 1

Related Questions