CODEWITHSUNDEEP
sqlpostgresql
Rima
Rima

Reputation: 460

Summing the SUM of two tables

I've two tables:

SELECT a."StartTime", a."DiameterTotal"[1]
FROM sdp1a_cip a
WHERE date_trunc('minute', a."StartTime") = TIMESTAMP '2014-07-12 23:51:00'

"2014-07-12 23:51:00";4224
"2014-07-12 23:51:10";4251
"2014-07-12 23:51:20";4207
"2014-07-12 23:51:31";4204
"2014-07-12 23:51:41";4233
"2014-07-12 23:51:51";4174

and 

SELECT b."StartTime", b."DiameterTotal"[1]
FROM sdp1b_cip b
WHERE date_trunc('minute', b."StartTime") = TIMESTAMP '2014-07-12 23:51:00'

"2014-07-12 23:51:02";4235
"2014-07-12 23:51:12";4100
"2014-07-12 23:51:22";4266
"2014-07-12 23:51:32";4292
"2014-07-12 23:51:42";4199
"2014-07-12 23:51:52";4109

I want an output like this:

"2014-07-12 23:51:0";50494

which is the sum of the SUM(DiameterTotal[1]) of each table grouped by the date_trunc('minute',..) of the time stamp.

now, I've tried multiple combinations (no sql expert here) but all are giving me the sum of 36 rows (cartesian product of the rows of the two tables).

one of the queries I've tried is:

SELECT date_trunc('minute',sdp1a_cip."StartTime"), SUM(sdp1a_cip."DiameterTotal"[1]) + SUM(sdp1b_cip."DiameterTotal"[1])
FROM sdp1a_cip, sdp1b_cip
WHERE date_trunc('minute', sdp1a_cip."StartTime") = TIMESTAMP '2014-07-12 23:51:00'
AND date_trunc('minute',sdp1a_cip."StartTime") = date_trunc('minute',sdp1b_cip."StartTime")
group by date_trunc('minute',sdp1a_cip."StartTime")

EDIT

this query worked but it's taking too long:

SELECT "StartTime", SUM(recCount) FROM (
  SELECT date_trunc('minute', a."StartTime") as "StartTime", SUM(a."DiameterTotal"[1]) as recCount
  FROM sdp1a_cip a
  WHERE date_trunc('minute', a."StartTime") = TIMESTAMP '2014-07-12 23:51:00'
  Group By date_trunc('minute', a."StartTime")

  UNION ALL

  SELECT date_trunc('minute', b."StartTime") as "StartTime", SUM(b."DiameterTotal"[1]) as recCount
  FROM sdp1b_cip b
  WHERE date_trunc('minute', b."StartTime") = TIMESTAMP '2014-07-12 23:51:00'
  Group By date_trunc('minute', b."StartTime")
) g
Group By g."StartTime"

Upvotes: 1

Views: 95

Answers (1)

Christian Phillips
Christian Phillips

Reputation: 18769

Have you tried UNION ALL?

SELECT "StartTime", SUM(recCount) FROM (
  SELECT date_trunc('minute', a."StartTime") as "StartTime", SUM(a."DiameterTotal"[1]) as recCount
  FROM sdp1a_cip a
  WHERE date_trunc('minute', a."StartTime") = TIMESTAMP '2014-07-12 23:51:00'
  Group By date_trunc('minute', a."StartTime")

  UNION ALL

  SELECT date_trunc('minute', b."StartTime") as "StartTime", SUM(b."DiameterTotal"[1]) as recCount
  FROM sdp1b_cip b
  WHERE date_trunc('minute', b."StartTime") = TIMESTAMP '2014-07-12 23:51:00'
  Group By date_trunc('minute', b."StartTime")
) g
Group By g."StartTime"

I haven't tested this.

Upvotes: 3

Related Questions