Results 1 to 10 of 10
  1. #1
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    Unanswered: sum two (entire) tables by common id

    Hi I have 2 SQL queries that after selection they return
    id_user, points, played, won, lost

    So I have to tables with the same users ID but in different order...(one is for games played at home and the other away).
    So is there a way to use them as subquerys and sum each row where the id_user is the same?

    Thanks!!

  2. #2
    Join Date
    Jun 2005
    Posts
    23
    Try this
    Code:
    SELECT user_id,SUM(points),SUM(played),SUM(won),SUM(lost)
    FROM
      (
         SELECT user_id,points,played,won,lost FROM table1
         UNION ALL
         SELECT user_id,points,played,won,lost FROM table2
      ) AS t
    GROUP BY user_id

  3. #3
    Join Date
    Jun 2005
    Posts
    23
    Or
    Code:
    SELECT user_id,points,played,won,lost
    FROM
      (
         SELECT t.user_id,SUM(t.points+t1.points) AS points,
                    SUM(t.played+t1.played) AS played,
                    SUM(t.won+t1.won) AS won,
                    SUM(t.lost+t1.lost) AS lost
          FROM table1 t
          INNER JOIN
          table2 t1
          ON t.user_id=t1.user_id
          GROUP BY t.user_id
      ) AS tb

  4. #4
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    Not to lecture you, but what's the need for two tables for games? This will just create redundant information.

    Rather than having a `homegames` table and an `awaygames` table, why not just have a `games` table with a `hometeamid` and `awayteamid`? Or am I missing something?

  5. #5
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    thanks...

    Thanks tombell!! I, i'm trying that but it's not working...


    Not to lecture you, but what's the need for two tables for games? This will just create redundant information.

    Rather than having a `homegames` table and an `awaygames` table, why not just have a `games` table with a `hometeamid` and `awayteamid`? Or am I missing something?
    Thanks, but I only have 1 table...but the only way I got to create the standings table was from 2 selects...one of home matches and one with away matches...

    The thing is I have 1 table of matches... id_local, golas_local, id_visit, golas_visit and with that i'm building the tournament standings....

  6. #6
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    explanation

    my main source for bulding the standings table or gird is:

    id_match, id_usuario1, id_usuario2, goles1, goles2

    then with
    Code:
    $sql_local = 'SELECT id_usuario1 as id_usuario,'
    			. ' SUM(CASE '
    			. ' WHEN goles1 - goles2 > 0 THEN 3'
    			. ' WHEN goles1 - goles2 = 0 THEN 1'
    			. ' ELSE 0'
    			. ' END ) pts,'
    			. ' SUM(CASE '
    			. ' WHEN goles1 - goles2 > 0 THEN 1'
    			. ' ELSE 0'
    			. ' END ) ganados,'
    			. ' SUM(CASE '
    			. ' WHEN goles1 - goles2 = 0 THEN 1'
    			. ' ELSE 0'
    			. ' END ) empates, '
    			. ' SUM(CASE '
    			. ' WHEN goles1 - goles2 < 0 THEN 1'
    			. ' ELSE 0'
    			. ' END ) perdidos, '
    			. ' SUM(goles1) gf, '
    			. ' SUM(goles2) gc '
    			. ' FROM partidos GROUP BY  id_usuario1 ORDER By pts DESC LIMIT 0, 30';		
    WHEN goles1 - goles2 > 0 THEN 3 WHEN goles1 - goles2 = 0 THEN 1 ELSE 0 END ) pts, SUM(CASE WHEN goles1 - goles2 > 0 THEN 1 ELSE 0 END ) ganados, SUM(CASE WHEN goles1 - goles2 = 0 THEN 1 ELSE 0 END ) empates, SUM(CASE WHEN goles1 - goles2 < 0 THEN 1 ELSE 0 END ) perdidos FROM partidos GROUP BY id_usuario1 ORDER By pts DESC LIMIT 0, 30";*/
    		$sql_visitante = 'SELECT id_usuario2 as id_usuario,'
    			. ' SUM(CASE WHEN goles2 - goles1 > 0 THEN 3'
    			. ' WHEN goles2 - goles1 = 0 THEN 1'
    			. ' ELSE 0'
    			. ' END ) pts,'
    			. ' SUM(CASE WHEN goles2 - goles1 > 0 THEN 1'
    			. ' ELSE 0'
    			. ' END ) ganados,'
    			. ' SUM(CASE WHEN goles2 - goles1 = 0 THEN 1'
    			. ' ELSE 0'
    			. ' END ) empates, '
    			. ' SUM(CASE WHEN goles2 - goles1 < 0 THEN 1'
    			. ' ELSE 0'
    			. ' END ) perdidos, '
    			. ' SUM(goles2) gf, '
    			. ' SUM(goles1) gc '
    			. ' FROM partidos GROUP BY id_usuario2 ORDER BY pts DESC LIMIT 0, 30';
    I get 2 tables.....then I merge them and order again by points...

  7. #7
    Join Date
    Apr 2005
    Location
    Baltimore, MD
    Posts
    297
    How about something like this?

    Code:
    SELECT
    	teamid
    	, SUM(goals_for) as goals_scored
    	, SUM(goals_against) as goals_allowed
    	, SUM(win) as wins
    	, SUM(loss) as losses
    	, SUM(tie) as ties
    FROM
    	((
    	SELECT
    		id_local as teamid
    		, golas_local as goals_for
    		, golas_visit as goals_against
    		, (SELECT IF( (golas_local - golas_visit) > 0, 1, 0 )) as win
    		, (SELECT IF( (golas_local - golas_visit) < 0, 1, 0 )) as loss
    		, (SELECT IF( (golas_local - golas_visit) = 0, 1, 0 )) as tie
    	FROM
    		games
    	)
    	UNION ALL
    	(
    	SELECT	
    		id_visit as teamid
    		, golas_visit as goals_for
    		, golas_local as goals_against
    		, (SELECT IF( (golas_visit - golas_local) > 0, 1, 0 )) as win
    		, (SELECT IF( (golas_visit - golas_local) < 0, 1, 0 )) as loss
    		, (SELECT IF( (golas_visit - golas_local) = 0, 1, 0 )) as tie
    	FROM
    		games
    	)) AS tbl
    GROUP BY 
    	teamid
    ORDER BY 
    	wins DESC
    	, losses DESC
    	, ties DESC
    	, goals_scored DESC
    	, goals_allowed DESC
    That should give you everything in one sql statement (save for some translation ).

    (also, for your php code, you don't need to end and concatenate the string at the end of each line break.)

  8. #8
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    mmm...

    maybe there is a problem with mysql version......?

    #1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT
    id_local as teamid
    , goles_local as goals_for
    , g

  9. #9
    Join Date
    Feb 2004
    Location
    Argentina
    Posts
    58

    yep...

    Yes... I think it's the version MySQL 4.0.20...... I think it does'n support subqueries very well...

  10. #10
    Join Date
    Jun 2005
    Posts
    23
    Quote Originally Posted by diegolaz
    So is there a way to use them as subquerys and sum each row where the id_user is the same?
    By saying subquery, one believes that you are running mysql 4.1.x and posts solutions that are supported by the version you are running on.
    You should be more specific next time you post a question.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •