CODEWITHSUNDEEP
sqloracle-database
Mohamad Ghanem
Mohamad Ghanem

Reputation: 599

Get the max count of rows per hours

I have the following table:

"ITEMS"   "EXPIRY_DATE"
1         30-AUG-17 07.00.00.000000000 AM
1         30-AUG-17 07.15.22.706000000 AM
1         30-AUG-17 07.44.51.794000000 AM
1         30-AUG-17 08.57.11.426000000 AM
1         30-AUG-17 09.57.24.810000000 AM
1         31-AUG-17 06.57.34.236000000 AM
1         31-AUG-17 06.57.42.121000000 AM
1         31-AUG-17 07.57.48.978000000 AM

then, I am getting the count of rows (per hour everyday) using the following query:

SELECT COUNT(*), EXTRACT(HOUR FROM EXPIRY_DATE), EXTRACT(DAY FROM EXPIRY_DATE)
FROM TABLE1 
GROUP BY EXTRACT(HOUR FROM EXPIRY_DATE), EXTRACT(DAY FROM EXPIRY_DATE)
ORDER BY EXTRACT(DAY FROM EXPIRY_DATE);

the result of the above query using the above sample data is:

"COUNT(*)"  "EXTRACT(HOURFROMEXPIRY_DATE)"  "EXTRACT(DAYFROMEXPIRY_DATE)"
3   7   30
1   8   30
1   9   30
2   6   31
1   7   31

how can I return the max count of rows per hour for everyday ? I mean, I want the following output:

"COUNT(*)"  "MAX hour"  "DAY"
3   7   30
2   6   31

Upvotes: 0

Views: 632

Answers (2)

Radim Bača
Radim Bača

Reputation: 10701

Use HAVING

SELECT COUNT(*), EXTRACT(HOUR FROM EXPIRY_DATE) eh, EXTRACT(DAY FROM EXPIRY_DATE) ed
FROM TABLE1 
GROUP BY EXTRACT(HOUR FROM EXPIRY_DATE), EXTRACT(DAY FROM EXPIRY_DATE)
HAVING COUNT(*) >= ALL
(
    SELECT COUNT(*)
    FROM TABLE1 t2
    WHERE EXTRACT(DAY FROM t2.EXPIRY_DATE) = EXTRACT(DAY FROM table1.EXPIRY_DATE)
    GROUP BY EXTRACT(HOUR FROM t2.EXPIRY_DATE), 
)

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521599

Use RANK:

SELECT
    t.cnt,
    t.hour,
    t.day
FROM
(
    SELECT
        COUNT(*) AS cnt,
        EXTRACT(HOUR FROM EXPIRY_DATE) AS hour,
        EXTRACT(DAY FROM EXPIRY_DATE) AS day,
        RANK() OVER (PARTITION BY EXTRACT(DAY FROM EXPIRY_DATE)
                     ORDER BY COUNT(*) DESC) rank
    FROM TABLE1 
    GROUP BY EXTRACT(HOUR FROM EXPIRY_DATE), EXTRACT(DAY FROM EXPIRY_DATE)
) t
WHERE t.rank = 1
ORDER BY t.day;

Upvotes: 2

Related Questions