CODEWITHSUNDEEP
phpmysql
knzo
knzo

Reputation: 65

sum of grouped rows of two different tables

I'm somehow stuck on a very simple task..

I have two tables:

TABLE A:
name  time
name1 100
name2  50
name1 200
name3 100
name2  50

TABLE B:
name time
name3 100
name1  50
name3 200
name2 100
name1  50

All I want to have is a list of the first 2 names with highest time in total!

Something like this (but this dowsn't work ofc)

SELECT  a.name 
        , SUM(a.time) as time1
        , (SELECT SUM(time) FROM table2 b WHERE b.name = a.name GROUP BY a.name) as time2
        , time1 + time2 as total
FROM table1 a
GROUP BY a.name
ORDER BY total DESC
LIMIT 2

Thanks in advance!

Upvotes: 0

Views: 33

Answers (2)

svvac
svvac

Reputation: 6124

I'd go with something like:

SELECT
  name,
  SUM(time) AS total_time
  FROM (
    SELECT name, time FROM table_a
    UNION ALL
    SELECT name, time FROM table_b
  ) AS u
  GROUP BY name
  ORDER BY total_time DESC
  LIMIT 2
;

Here's a working sqlfiddle with your data.

Upvotes: 1

Rob
Rob

Reputation: 31

Try this one...

SELECT name, SUM( timeA + timeB ) AS total
FROM (
    SELECT name, time as timeA, 0 as timeB
    FROM a
    UNION ALL
    SELECT name, 0 as timeA, time as timeB
    FROM b
) AS times
GROUP BY name
ORDER BY total DESC

Upvotes: 0

Related Questions