Getting totals from 2 tables based on 1 table 1

[thepixel]

I'm trying to lookup some totals by state in these 3 tables. We have a league table, which contains the state column, then we have a team table which has a reference back to league, then we have a member table which references back to the team table. I'm trying to figure out how to get totals on all teams in each state and all members in each state with only one query.

The closest I can get is:

SELECT `l`.`state` AS `state`, COUNT(`t`.`team_id`) AS `teams`, COUNT(`m`.`mem_id`) AS `members`
FROM `leagues` AS `l`
INNER JOIN `teams` AS `t` ON `s`.`league_id` = `t`.`team_league_id`
LEFT OUTER JOIN `members` AS `m` ON `t`.`team_id` = `m`.`mem_team_id`
WHERE LENGTH(`l`.`state`) > 0
GROUP BY `l`.`state`
ORDER BY `l`.`state` ASC

I just don't think my numbers are right because when I remove the member table from the query my team totals are different. What am I missing here?

 

[IanWaterman]

YOU will get a team ID for each member, perhaps you should be using

COUNT(distinct `m`.`mem_id`)

Ian

 

[r937]

SELECT l.state
     , COUNT(*)            AS state_leagues
     , SUM(league_teams)   AS state_teams
     , SUM(league_members) AS state_members
  FROM leagues AS l
INNER
  JOIN ( SELECT t.team_league_id
              , COUNT(*) AS league_teams
              , SUM(team_members) AS league_members
           FROM teams AS t
         LEFT OUTER 
           JOIN ( SELECT mem_team_id
                       , COUNT(*) AS team_members
                    FROM members
                  GROUP
                      BY mem_team_id ) AS m 
             ON m.mem_team_id = t.team_id
         GROUP
             BY t.team_league_id ) AS tm
    ON tm.team_league_id = l.league_id
 WHERE LENGTH(l.state) > 0
GROUP 
    BY l.state
ORDER 
    BY l.state

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[thepixel]

Wow. This is incredible. I actually just sent an email to my boss to order your book. (... and just got a reply back to go ahead and order it)

So the other thing is, After reviewing the table structure (rather than the old code), there's actually 4 levels to this whole relationship schema.

There are Leagues (groups) (s.league_id), then there are Leaders (l.lead_id). Leagues can have multiple Leaders (l.lead_league_id = s.league_id). Then there are Teams (t.team_id). Leaders can have multiple Teams (t.team_lead_id = l.lead_id). Then there are Members (m.mem_id). Teams (obviously) can have multiple members (m.mem_team_id = t.team_id).

So I *tried* to modify this glorious statement, but I may have broke it. MAN I wish I knew as much as you about SQL. It's not the subselects that confuse me (although the syntax does make it a bit difficult to read), but which join to use where is what get's confusing to me. Then mixing the use of JOINs and Subselects is so far above my head it's just rediculous) Anyway, I was going to paste my modifications here but I believe I jacked it up so bad, it would only cause confusion.

Also, I was going to recommend setting up a script on your website that can track where people are clicking to buy your book. For example, if you were to have

http://www.rudy.ca/book/tek-tips/

then redirect it to the Amazon.com site, you could track that I clicked your link from TekTips. Just an idea. Thanks man! You're really the best!

 

[r937]

thanks for the very kind words

if i weren't quite so lazy i'd get off my duff and develop

http://simply-sql.com

(which i purchased to promote the book)

here's 4 levels, i hope i did not mess anything up...

SELECT s.state
     , COUNT(*)               AS state_leagues
     , SUM(lm.league_leaders) AS state_leaders
     , SUM(lm.league_teams)   AS state_teams
     , SUM(lm.league_members) AS state_members
  FROM leagues AS s
LEFT OUTER
  JOIN ( SELECT l.lead_league_id
              , COUNT(*) AS league_leaders
              , SUM(tm.lead_teams) AS league_teams
              , SUM(tm.lead_members) AS league_members
           FROM leaders AS l
         LEFT OUTER
           JOIN ( SELECT t.team_lead_id
                       , COUNT(*) AS lead_teams
                       , SUM(m.team_members) AS lead_members
                    FROM teams AS t
                  LEFT OUTER 
                    JOIN ( SELECT mem_team_id
                                , COUNT(*) AS team_members
                             FROM members
                           GROUP
                               BY mem_team_id ) AS m 
                      ON m.mem_team_id = t.team_id
                  GROUP
                      BY t.team_lead_id ) AS tm
             ON tm.team_lead_id = l.lead_id
         GROUP
             BY l.lead_league_id ) AS lm
    ON lm.lead_league_id = s.league_id
 WHERE LENGTH(s.state) > 0
GROUP 
    BY s.state
ORDER 
    BY s.state

noitice they're all LEFT OUTER JOINs now

please let me know if there are any problems



r937.com | rudy.ca
Buy my new book Simply SQL from Amazon