We help IT Professionals succeed at work.

MySQL Union select statements

that1guy15
that1guy15 asked
on
Hey all,

First off I am not by any means a DBA or a database guy. I can stumble around in MySQL enough to get what i want and break stuff :)

I have a database that is used for system monitoring. Events are entered into one table (status) and then over time "aged out" and moved to a second table (history). I have a query that will return the total number of each event over a given time frame. I use this to basically get a top 10 errors report for a given time frame.

This works great when i run the query against one table or the other. My problem is when i use the UNION ALL statement and combine the two into a single query. the query runs fine and gives me the proper results for each table. But it does not merge the same events from each table. So the returned list has duplicate events, one from each table. I would like to get only a single event for both tables and its combined count.

Here is the query i am running and a sample of what it returns.

Thanks for your help!

SELECT COUNT(summary) AS total_count, summary FROM history 
 WHERE lastTime>(UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 10 HOUR))) 
 AND severity = 3 GROUP BY summary UNION ALL 
SELECT COUNT(summary) AS total_count, summary FROM status 
 WHERE lastTime>(UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 10 HOUR))) 
 AND severity = 3 GROUP BY summary ORDER by total_count DESC;



+-------------+------------------------+
| total_count | summary                |
+-------------+------------------------+
|          60 | TIMEOUT14              |
|          54 | TIMEOUT14              |
|          53 | COMMERROR              |
|          48 | COMMERROR              |
|          48 | DURATIONTIMEOUT        |
+-------------+------------------------+

Open in new window

Comment
Watch Question

Commented:
Select COUNT(summary) AS total_count
FROM (
SELECT summary
FROM history
 WHERE lastTime>(UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 10 HOUR)))
 AND severity = 3
UNION ALL
SELECT summary
FROM status
 WHERE lastTime>(UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 10 HOUR)))
 AND severity = 3
)
GROUP BY summary
ORDER by total_count DESC;



If this does not work try replacing the UNION ALL with just UNION

Commented:
Write the first query to a temp table:
SELECT COUNT(summary) AS total_count, summary
FROM history INTO #temp
WHERE lastTime>(UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 10 HOUR)))
AND severity = 3
GROUP BY summary;

Insert the results from the second query to the temp table:
INSERT INTO #temp
SELECT COUNT(summary) AS total_count, summary FROM status
WHERE lastTime>(UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 10 HOUR)))
AND severity = 3
GROUP BY summary;

Then go a group by and sum on the temp table:
SELECT distinct summary, SUM(total_count) as total_count
GROUP BY summary
ORDER BY total_count DESC;
Commented:
SELECT COUNT(T.summary) AS total_count, T.summary FROM
       (Select summary, lastTime, severity from history union all
        Select summary, LastTime, severity from status) T
 WHERE lastTime>(UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 10 HOUR)))
 AND severity = 3
GROUP BY SUMMARY
ORDER by total_count DESC

Author

Commented:
So more than one way to skin a cat :)

@brad2575
Im not really following what your query is doing. But when i run it a get "Every derived table must have its own alias"

@iph572
I get a syntex error at the WHERE statement. I like this approach, is the temp DB removed after the query or will it need to be purged manually? Im not sure if it matters but im running version 5.0.45

@dgmg
It works! but i have no clue what is going on with the COUNT(T.summary) could you explain?

thanks guys for your input. Im learning this stuff as i go and the more input on what is going on the more of a help it is to me.

I really appreciate your help!
Commented:
try this:

Select COUNT(summary) AS total_count
FROM (
SELECT summary
FROM history
 WHERE lastTime>(UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 10 HOUR)))
 AND severity = 3
UNION ALL
SELECT summary
FROM status
 WHERE lastTime>(UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 10 HOUR)))
 AND severity = 3
) as RPT1
GROUP BY summary
ORDER by total_count DESC;
Commented:
The secret is to UNION before you count.  Aside from the minor syntax flaw, Brad's solution is similar.  Stylewise, I prefer to consolidate the where clause, thus my way to do the skinin'.

I copied Count(T.Summary) from your SQL; it's just a way to count the rows where summary is not null.  T. is an alias for the UNION of your two tables.   If you want to count the rows regardless nulls in summary, you could just as well do count(*).  

Commented:
dgmg,

Yea yours would work good (easier to read) but depending on the tables/queries I found that adding the where clause like I have it can speed things up a lot and it makes it so you dont have to select as many fields (all depends on the amount of data you are dealing with though, if not much yours would be better/easier to read, if a lot mine would speed things up or at least use less memory/CPU.

Author

Commented:
@brad2575

It works but it only returns the total_count and not the summary field.

i appreciate everyones input. this query is only used about once or twice a day and the tables only have 2000 events in them at any given time. So we are talking pretty small. Im going with dgmg's solution for its simplicity

thanks for the input guys im starting to get the hang of the SQL stuff! I guess that makes me that much more dangerous to databases everywhere. Its time to move on to learning the DROP function :)

have a good weekend all!


Author

Commented:
Thanks everyone for the help!