#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    80
    Rep Power
    7

    Using Multiple Selects in One Query


    I have the following query below, which works great for what I need. But i'd like to add another column to my results, that produces the number of players that were used to get each rounds' resulting score. For example, if user 32 got 45 points in round 2 and had 4 players on his/her team that accumulated that 45 points, i'd like to display that number of 4.

    I know how to get that count in its own separate query, but cant seem to add it into what i've already got so i can produce the results in a nice looking table. Any suggestions?

    Thanks.

    Code:
    SELECT
    id
    , firstname
    , lastname
    , Rd1
    , Rd2
    , Rd3
    , Rd4
    , (Rd1 + Rd2 + Rd3 + Rd4) AS Total
    FROM (
    SELECT
    u.id
    , u.firstname
    , u.lastname
    , SUM(
    CASE WHEN
    s.round = 1
    THEN
    CASE WHEN 
    	s.pass_yd>299
    THEN
    	(5+(TRUNCATE((s.pass_yd/25),0)))
    ELSE
    	TRUNCATE((s.pass_yd/25),0)
    END
    + (s.pass_td*6)
    - (s.pass_int*2)
    + CASE WHEN 
    	s.rush_yd>99
    THEN
    	(5+(TRUNCATE((s.rush_yd/10),0)))
    ELSE
    	TRUNCATE((s.rush_yd/10),0)
    END
    + (s.rush_td*6)
    + CASE WHEN 
    	s.rec_yd>99
    THEN
    	(5+(TRUNCATE((s.rec_yd/10),0)))
    ELSE
    	TRUNCATE((s.rec_yd/10),0)
    END
    + (s.rec_td*6)
    - (s.fum*2)
    + s.pat
    +(s.fg_139*3)
    +(s.fg_4049*4)
    +(s.fg_5059*5)
    +(s.fg_60*6)
    +s.def_sack
    +(s.def_int*2)
    +(s.def_fum*2)
    +(s.def_td*6)
    +(s.def_safe*2)
    +(s.def_block*2)
    + CASE WHEN
    	p.pos != 'DEF'
    THEN
    	0
    ELSE
    	CASE WHEN
    		s.def_pts='--'
    	THEN
    		0
    	WHEN
    		s.def_pts=0
    	THEN
    		15
    	WHEN 
    		s.def_pts<7
    	THEN
    		10
    	WHEN 
    		s.def_pts<14
    	THEN
    		7
    	WHEN 
    		s.def_pts<21
    	THEN
    		4
    	WHEN 
    		s.def_pts<28
    	THEN
    		1
    	WHEN 
    		s.def_pts<35
    	THEN
    		0
    	WHEN 
    		s.def_pts<45
    	THEN
    		-1
    	ELSE
    		-5
    	END
    END
    ELSE
    0
    END
    ) AS Rd1
    ** above is repeated for Rd2-Rd4 **
    FROM
    players AS p
    LEFT JOIN
    entries AS e ON e.player_id = p.id
    JOIN 
    users AS u ON u.id = e.user_id 
    LEFT JOIN 
    stats AS s ON s.player_id = p.id
    GROUP BY
    u.id
    ) AS entry
    ORDER BY
    Total DESC, lastname ASC, firstname ASC
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,244
    Rep Power
    4279
    Originally Posted by zubes1001
    I know how to get that count in its own separate query, but cant seem to add it into what i've already got so i can produce the results in a nice looking table.
    don't know yet

    can you show us this separate query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    80
    Rep Power
    7
    Code:
    SELECT
    COUNT (s.player_id) as counts
    FROM
    stats as s
    JOIN
    entries as e on e.player_id = s.player_id
    JOIN
    users as u on u.id = e.user_id
    WHERE
    s.round = 1
    GROUP BY 
    u.id
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,244
    Rep Power
    4279
    not sure if this is gonna do it, as i wasn't sure of the cardinality of all your table relationships
    Code:
    SELECT id
         , firstname
         , lastname
         , Rd1
         , Rd2
         , Rd3
         , Rd4
         , Rd1 + Rd2 + Rd3 + Rd4 AS Total
         , counts
      FROM ( SELECT u.id
                  , u.firstname
                  , u.lastname
                  , COUNT( DISTINCT CASE WHEN s.round = 1
                                    THEN s.player_id
                                    ELSE NULL END ) AS counts
                  , SUM( CASE WHEN s.round = 1
                              THEN CASE WHEN s.pass_yd > 299
                                        THEN (5+(TRUNCATE((s.pass_yd/25),0)))
                                        ELSE TRUNCATE((s.pass_yd/25),0) END
                                 + s.pass_td * 6
                                 - s.pass_int * 2
                                 + CASE WHEN s.rush_yd > 99
                                        THEN TRUNCATE(s.rush_yd/10,0) + 5
                                        ELSE TRUNCATE(s.rush_yd/10,0) END
                                 + (s.rush_td*6)
                                 + CASE WHEN s.rec_yd > 99
                                        THEN TRUNCATE(s.rec_yd/10,0) + 5
                                        ELSE TRUNCATE(s.rec_yd/10,0) END
                                 + s.rec_td * 6
                                 - s.fum * 2
                                 + s.pat
                                 + s.fg_139 * 3
                                 + s.fg_4049 * 4
                                 + s.fg_5059 * 5
                                 + s.fg_60 * 6
                                 + s.def_sack
                                 + s.def_int * 2
                                 + s.def_fum * 2
                                 + s.def_td * 6
                                 + s.def_safe * 2
                                 + s.def_block * 2
                                 + CASE WHEN p.pos <> 'DEF'
                                        THEN 0
                                        ELSE CASE WHEN s.def_pts = '--'
                                                  THEN 0
                                                  WHEN s.def_pts = 0
                                                  THEN 15
                                                  WHEN s.def_pts < 7
                                                  THEN 10
                                                  WHEN s.def_pts < 14
                                                  THEN 7
                                                  WHEN s.def_pts < 21
                                                  THEN 4
                                                  WHEN s.def_pts < 28
                                                  THEN 1
                                                  WHEN s.def_pts < 35
                                                  THEN 0
                                                  WHEN s.def_pts < 45
                                                  THEN -1
                                                  ELSE -5 END
                                        END
                              ELSE 0 END
                       ) AS Rd1
    ** above is repeated for Rd2-Rd4 **
               FROM players AS p
             INNER 
               JOIN stats AS s 
                 ON s.player_id = p.id
             INNER 
               JOIN entries AS e 
                 ON e.player_id = p.id
             INNER
               JOIN users AS u 
                 ON u.id = e.user_id 
             GROUP 
                 BY u.id  ) AS entry
    ORDER 
        BY Total DESC
         , lastname ASC
         , firstname ASC
    try it with the DISTINCT and without

    Comments on this post

    • NotionCommotion agrees : Wow! Now that is a query!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    80
    Rep Power
    7
    That definitely worked, with both distinct and without it.

    Thanks a ton.

IMN logo majestic logo threadwatch logo seochat tools logo