CODEWITHSUNDEEP
mysqlsqldatemax
tkyass
tkyass

Reputation: 3186

find max value among different tables group by date

I have 4 tables for different dates, the tables looks like this:

enter image description here

what I'm trying to do is to find the maximum tps for each service_name,function_name among all four days according to hour. for example in the figure I posted there is service_name(BatchItemService) in first raw that have (getItemAvailability) as function_name in date 13-06-12 01. I have same service_name for same function_name in all the other 3 tables for the same hour "01" but with different days, like day 13,14,15. I want to find maximum tps for this service_name,function_name set for hour "01" among all the four days. I tried this, but it give me incorrect result.

SELECT 

    t.service_name,
    t.function_name,
t.date,
    max(t.tps)
FROM
    (SELECT 
         service_name, function_name, date, tps
    FROM
        trans_per_hr_2013_06_12

UNION ALL 
    SELECT 
         service_name, function_name, date,tps
    FROM
        trans_per_hr_2013_06_13
GROUP BY  service_name,function_name,date
UNION ALL 
    SELECT 
         service_name, function_name,date, tps
    FROM
        trans_per_hr_2013_06_14

UNION ALL 
    SELECT 
         service_name, function_name, date, tps
    FROM
        trans_per_hr_2013_06_15

UNION ALL 
    SELECT 
         service_name, function_name,date, tps
    FROM
        trans_per_hr_2013_06_16

    ) t
GROUP BY  t.service_name,t.function_name,hour(t.Date);

Thanks a lot...

Upvotes: 0

Views: 280

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269823

Your query looks like it should be returning what you want.

One possible issue is the type of the date column. As shown in the output, this looks like it might be stored as a character string rather than a date. If so, the following would work for the group by statement (assuming the format is as shown: DD-MM-YY H).

GROUP BY  t.service_name,t.function_name, right(t.Date, 2);

As Bohemian says in the comment, this is not a good data structure. You have parallel tables and you are storing the date both in the table name and in a column. You should learn about table partitioning. This is a way that you can store different days in different files, but still have MySQL interpret them as one table. It would probably greatly simplify your using this data.

Upvotes: 1

Related Questions