NatBI - 1 year ago 56

SQL Question

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 Source

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.

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
```