CODEWITHSUNDEEP
sqloracle-database
fjkjava
fjkjava

Reputation: 1464

Daily max and min not group by date

I have a table like 

Name Date       Count
A    01/01/2019 3 
A    01/01/2019 9
A    01/05/2019 5
A    01/05/2019 6
A    01/17/2019 1
A    01/25/2019 8
B    01/01/2019 3 
B    01/05/2019 4
B    01/17/2019 2
B    01/25/2019 9
A    02/01/2019 3 
A    02/01/2019 6
A    02/05/2019 1
A    02/17/2019 1
A    02/25/2019 10
B    02/01/2019 2 
B    02/05/2019 4
B    02/17/2019 4
B    02/17/2019 9
B    02/25/2019 6

Date is duplicate here, I have another column which makes it unique but that need not to be considered here for the result.

I want to get following data

Name Total Max(Daily Total) Min(Daily Total)
A    53    12   1
B    43    13   2

group by Name and sum(Count) gives me first two columns but not sure how can I get third and fourth columns?

Upvotes: 0

Views: 46

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You seem to want two levels of aggreation:

select name, sum(sum_count) as total,
       max(sum_count) as max_daily,
       min(sum_count) as min_daily
from (select name, date, sum(count) as sum_count
      from t
      group by name, date
     ) nd
group by name;

Upvotes: 3

Related Questions