Jackddddd Jackddddd - 26 days ago 7
SQL Question

SQL select all ids with the max value, then count how many rows are associated with each id

Suppose I have the following table HASCO:

Table HASCO

+--------+------+-----+
|PID |Amount|Date |
+--------+------+-----+
|1 |1000 |Date1|
+--------+------+-----+
|1 |8000 |Date2|
+--------+------+-----+
|2 |8000 |Date3|
+--------+------+-----+
|2 |3000 |Date4|
+--------+------+-----+
|2 |4000 |Date5|
+--------+------+-----+
|3 |4000 |Date6|
+--------+------+-----+


I wanna get the following result:

+--------+--------+
|PID |numTours|
+--------+--------+
|1 |2 |
+--------+--------+
|2 |3 |
+--------+--------+


PID 1 and 2 both have the maximum amount 8000, then PID 1 has 2 rows and PID 2 has three rows.

I tried the following query:

SELECT HASCO.PID, COUNT(*) AS numTour
FROM HASCO
GROUP BY HASCO.PID
HAVING HASCO.PID IN
(
SELECT HASCO.PID
FROM HASCO
WHERE HASCO.AMOUNT = (SELECT MAX(HASCO.AMOUNT) FROM HASCO)


This works on db2 but is there a better way to do it?

Answer

The sub-query in Having clause can simplified to

SELECT HASCO.PID, COUNT(*) AS numTour
FROM HASCO
GROUP BY HASCO.PID
HAVING max(HASCO.AMOUNT) = (SELECT MAX(HASCO.AMOUNT) FROM HASCO)

If DB2 supports windowed aggregate functions then

Select PID,count(1)
(
Select HASCO.PID,
       Max(AMOUNT)Over() as Max_amount,
       Max(AMOUNT) Over(Partition by PID) as Max_Pid_Amt
From HASCO
) A
Where Max_amount = Max_Pid_Amt
Group by PID