CODEWITHSUNDEEP
mysqlsqlgroup-byunion-all
user1569897
user1569897

Reputation: 437

How to join results of 'union all' in SQL

I have combined two tables using union all. However, the produced data has repeats. Is there a way to join the repeats?

For example, the resulting table after union all, t, looks like this":

__DAY__.....................__COUNT__
monday....................1
tuesday...................2
tuesday...................3
wednesday..............4
wednesday..............1
wednesday..............5

I would like it to look like this:

__DAY__.................__COUNT__
monday....................1
tuesday....................5
wednesday..............10

Also, I tried "group by t.day, t.count" and it does not work. And the "...." are for formatting purposes only, in order to have some sort of indent.

The query is the following.

SELECT   t1.hour, t1.day, t1.count
FROM
(
  (SELECT hour(time(FROM_UNIXTIME( timecode)))  AS hour,
   date(FROM_UNIXTIME( timecode))  AS day,
  (FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
   FROM sampleData
  WHERE hour(time(FROM_UNIXTIME( timecode)))>=14
   GROUP BY DAY)
 union all
  (SELECT hour(time(FROM_UNIXTIME( timecode))) as hour,
   date(FROM_UNIXTIME( timecode-86400) ) AS day,
   (FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'
  FROM sampleData
  WHERE hour(time(FROM_UNIXTIME( timecode)))< 14
   GROUP BY DAY)
)  t1
GROUP BY  t1.day, t1.count
HAVING COUNT(*)>0
ORDER BY t1.day

Upvotes: 1

Views: 730

Answers (3)

Himanshu
Himanshu

Reputation: 32602

Try this simple query:

SELECT day,sum(count) FROM Table1
Group by day

See this SQLFiddle

EDIT: For your query try this:

SELECT   t1.hour, t1.day, sum(t1.count)
FROM 
(
  (SELECT hour(time(FROM_UNIXTIME( timecode)))  AS hour,  
   date(FROM_UNIXTIME( timecode))  AS day, 
  (FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'  
   FROM sampleData  
  WHERE hour(time(FROM_UNIXTIME( timecode)))>=14  
   GROUP BY DAY)   
 union all   
  (SELECT hour(time(FROM_UNIXTIME( timecode))) as hour,  
   date(FROM_UNIXTIME( timecode-86400) ) AS day,  
   (FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'  
  FROM sampleData   
  WHERE hour(time(FROM_UNIXTIME( timecode)))< 14  
   GROUP BY DAY)  
)  t1  
GROUP BY  t1.day
HAVING COUNT(*)>0  
ORDER BY t1.day

Upvotes: 5

Joe G Joseph
Joe G Joseph

Reputation: 24046

You can try group by:

select DAY,sum(COUNT) as COUNT
from
( select DAY,COUNT from table1
  union all
 select DAY,COUNT from table1)a
group by a.DAY

EDIT:

For your query,

SELECT    t1.day, sum( t1.count )

.....
.....
GROUP BY  t1.day
HAVING COUNT(*)>0
ORDER BY t1.day

Upvotes: 0

sel
sel

Reputation: 4957

SUM the count and only group by day.

SELECT   t1.hour, t1.day, sum(t1.count)   
    FROM 
    (
      (SELECT hour(time(FROM_UNIXTIME( timecode)))  AS hour,  
       date(FROM_UNIXTIME( timecode))  AS day, 
      (FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'  
       FROM sampleData  
      WHERE hour(time(FROM_UNIXTIME( timecode)))>=14  
       GROUP BY DAY)   
     union all   
      (SELECT hour(time(FROM_UNIXTIME( timecode))) as hour,  
       date(FROM_UNIXTIME( timecode-86400) ) AS day,  
       (FROM_UNIXTIME( timecode)) AS original, COUNT(1)  as 'count'  
      FROM sampleData   
      WHERE hour(time(FROM_UNIXTIME( timecode)))< 14  
       GROUP BY DAY)  
    )  t1  
    GROUP BY  t1.day
    HAVING COUNT(*)>0  
    ORDER BY t1.day

Upvotes: 0

Related Questions