NatBI NatBI - 6 months ago 10
SQL Question

How do I find the max in each group when the calculation is done with count(*)?

I've been trying to find the max of each member of a group using advices from other posts, but seems this is a different problem as the counter is based on a count(*) not on an specific column.

My table has several columns; the ones I need: date and branch. Each record of the table represents a transaction in that branch. I would need to know for each date which is the branch with more transactions and how many they were done.

I started with:

Select date, branch, count(*) as total
from table
group by date, branch


I attempted a max(total) but this would just give me a row instead of one per group.

I tried joining with itself, something like this, but it doesn´t work because maxim is not recognized in the having clause:

Select date, branch, count(*) as maxim
(Select date, branch, count(*) as total
from table
group by date, branch) a
having maxim=max(total)
group by date, branch


Any idea?, thanks!

Answer

Try this:

select t1.date, t2.branch, t1.max_total
from (
  select date, max(total) as max_total
  from (
    select date, branch, count(*) as total 
    from mytable 
    group by date, branch) as x
  group by date    
) as t1    
join (
  select date, branch, count(*) as total 
  from mytable 
  group by date, branch
) as t2 on t1.date = t2.date and t1.max_total = t2.total

The idea is to use the query you started with twice as a derived table:

  • The first time you use it in order to get the maximum number per date
  • The second time you use it in order to extract the branch value having a total count equal to the maximum number. There may be more than one branches in case of ties.

Demo here

If DB2 supports window functions you may use the following, which, if applicable, is more efficient:

select date, branch, total
from (
  select date, branch, count(*) as total,
         rank() over (partition by date order by count(*) desc) as rn
  from mytable
  group by date, branch) as t
where t.rn = 1