0

I have a table of sold items, with specified name, day, hour and amount of items sold.

What I need to do is for every day find the hour in which the greatest number of items (of any type) was sold and return the two-columned table with day and amount of items.

What I managed to do is to compute the sum of items per hour, but how to pick the hour with maximum amount of items sold and show it together with the day?

here is my lousy sqlfiddle attempt: http://sqlfiddle.com/#!9/93b51/17/0

select day, hour, sum(amount) as suma 
from sold_items 
group by day, hour
Barmar
  • 741,623
  • 53
  • 500
  • 612
J. Doe
  • 3
  • 1

2 Answers2

1

You need to join your query with juergen d's query that gets the maximum hourly amount each day.

SELECT a.day, a.hour, a.suma
FROM (
    select day, hour, sum(amount) as suma 
    from sold_items 
    group by day, hour) AS a
JOIN (
    select day, max(suma) AS maxsuma
    from (
        select day, hour, sum(amount) as suma 
        from sold_items 
        group by day, hour) AS tmp
    group by day) AS b
ON a.day = b.day AND a.suma = b.maxsuma

DEMO

This follows the same pattern as SQL Select only rows with Max Value on a Column except that in this case, you're doing it with a subquery that calculates an aggregate, not the data coming directly from the table.

Community
  • 1
  • 1
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Works, thank you. Is there any possible way to do it easier though? – J. Doe Aug 03 '15 at 19:00
  • You could create a table with the aggregate data, and then query that table. Unfortunately, you can't use a temporary table for this, because you're not allowed to refer to a temporary table twice in a query. – Barmar Aug 03 '15 at 19:01
0
select day, max(suma)
from
(
    select day, hour, sum(amount) as suma 
    from sold_items 
    group by day, hour
) tmp
group by day

SQLFiddle

juergen d
  • 201,996
  • 37
  • 293
  • 362