Jackddddd - 1 year ago 67

SQL Question

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)

Answer Source

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