Subquery or join help needed

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Subquery or join help needed

Post by matthijs »

I have a set of tables: climbs, routes and areas. I'm having trouble getting the correct query for a summery. Maybe it's best to show what I have so far and what end-result I want.

Code: Select all

 
    $sql = "    SELECT r.`route_grade`, c.`climb_style`
                FROM  `mc_routes` r, `mc_routeclimbs` c, `mc_areas` a
                WHERE  c.`route_id` =  r.`route_id`
                AND r.`area_id` = a.`area_id`
                AND  c.`user_id` = :id
                ORDER BY r.`route_grade` DESC
            ";
 
which results in something like:

Code: Select all

 
route_grade | climb_style
7c  RP
7b+ RP
7b+ FL
7b+ RP
7b+ RP
7b  RP
7b  RP
7b  RP
7a+ OS
7a+ RP
7a+ OS
7a+ RP
7a+ OS
7a  OS
7a  OS
7a  RP
7a  FL
6c+ OS
6c+ OS
6c  OS
6b+ OS
6b+ OS
6b+ OS
6b+ OS
6b  FL
6b  OS
6b  OS
6b  OS
6b  OS
6b  OS
6a+ OS
6a+ OS
6a+ OS
6a  OS
6a  OS
6a  OS
6a  OS
5c  OS
5c  OS
 
Now what I would like is to group by the grades, get the total number per grade but also get the number of climbs in each style per grade:

Code: Select all

 
Grade |  Total |  OS | RP | FL
7c | 1 | 0 | 1 | 0
7b+ | 4 | 0 | 3 | 1
7b | 3 | 0 | 3 | 0
... etc
 
Hope it's clear what I want.

I have looked at using subqueries but so far without success. I could of course take the result set I have (shown above) and write some PHP loop with some logic to get the result I want, but if it's possible to do in in MySQL I'd prefer that.

Any ideas?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Subquery or join help needed

Post by Eran »

Maybe something like:

Code: Select all

 
SELECT r.route_grade AS grade, 
    SUM(CASE WHEN c.climb_style = 'OS' THEN 1 END) AS os,
    SUM(CASE WHEN c.climb_style = 'RP' THEN 1 END) AS rp,
    SUM(CASE WHEN c.climb_style = 'FL' THEN 1 END) AS fl,
    COUNT(*) AS total 
FROM mc_routes AS r, mc_routeclimbs as C, mc_areas as A
WHERE c.route_id = r.route_id
AND r.area_id = a.area_id
AND c.user_id = :id
ORDER BY r.route_grade DESC
GROUP BY r.route_grade
 
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Subquery or join help needed

Post by matthijs »

Thanks for your reply.

The query looks interesting. It does give a syntax error.

What is the THEN 1 END doing in

Code: Select all

SUM(CASE WHEN c.climb_style = 'OS' THEN 1 END) AS os,
I understand the CASE WHEN, but haven't seen the THEN 1 END syntax before.
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Subquery or join help needed

Post by matthijs »

pytrin, I was able to find the syntax error, it was in the last rows, just a switch of the GROUP By and ORDER BY

Code: Select all

 
    SELECT r.`route_grade` AS 'grade',
                SUM(CASE WHEN c.`climb_style` = 'OS' THEN 1 END) AS 'os',
                SUM(CASE WHEN c.`climb_style` = 'RP' THEN 1 END) AS 'rp',
                SUM(CASE WHEN c.`climb_style` = 'FL' THEN 1 END) AS 'fl',
                COUNT(*) AS 'total'
      FROM `mc_routes` AS r, `mc_routeclimbs` AS c, `mc_areas` AS a
      WHERE c.`route_id` = r.`route_id`
      AND r.`area_id` = a.`area_id`
      AND c.`user_id` = :id
      GROUP BY r.`route_grade`
      ORDER BY r.`route_grade` DESC
            
 
Thanks so much!

One question though: if you want, could you explain what happens in the

Code: Select all

SUM(CASE WHEN c.`climb_style` = 'FL' THEN 1 END) AS 'fl',
I'm reading through the manual now, but find it a bit hard to understand from that
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Subquery or join help needed

Post by Eran »

It's a little trick to add a conditional count. Instead of using the COUNT function, you use the SUM over a series of 1 and 0. The condition inside the sum returns 1 if it is true, 0 otherwise.
http://dev.mysql.com/doc/refman/5.0/en/ ... ement.html
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Subquery or join help needed

Post by onion2k »

I'd just group by the two things you want.

Code: Select all

SELECT r.`route_grade`, c.`climb_style`, COUNT(c.`climb_style`) AS total_climbs
FROM  `mc_routes` r, `mc_routeclimbs` c, `mc_areas` a
WHERE  c.`route_id` =  r.`route_id`
AND r.`area_id` = a.`area_id`
AND  c.`user_id` = :id
GROUP BY r.`route_grade`, c.`climb_style`
ORDER BY r.`route_grade` DESC
 
Then make the table with PHP.
matthijs
DevNet Master
Posts: 3360
Joined: Thu Oct 06, 2005 3:57 pm

Re: Subquery or join help needed

Post by matthijs »

@pytrin: thanks! I think I understand it (somewhat).

@onion2k: Thanks for your input.
Your query returns this:

Code: Select all

 
Grade | Style | Number
7c   RP  1
7b+  FL  1
7b+  RP  3
7b   RP  3
7a+  OS  3
7a+  RP  2
7a   OS  2
7a   FL  1
7a   RP  1
6c+  OS  2
6c   OS  1
6b+  OS  4
6b   OS  5
6b   FL  1
6a+  OS  3
6a   OS  4
5c   OS  2
 
How would you use PHP to get this:

Code: Select all

 
Grade | Total | OS | RP | FL 
7c   1       1  
7b+  4       3   1
7b   3       3  
7a+  5   3   2  
7a   4   2   1   1
6c+  2   2      
6c   1   1      
6b+  4   4      
6b   6   5       1
6a+  3   3      
6a   4   4      
5c   2   2  
 
That is what pytrin's query returns and what I meant to get.

Or, if what I want is what is shown in the second result set, do you still think I should use your query instead of pytrins?

(by the way, your query does give me some other ideas)

By the way 2: I must say queries like these are pretty cool!
Post Reply