CODEWITHSUNDEEP
sqloracle10g
Sheldon SW
Sheldon SW

Reputation: 120

SQL MAX of a COUNT (with other cols)

I have a database with 3 relations Offers, Products & Categories. I'm trying to find the day of the month with the max number of offered products. I need to return the Category Name (CN), Day with the max number of offers (OD) and the number of offers (PC).

I have this query:

SELECT CN, MAX(PC)
FROM
(
SELECT c.nombre as CN, EXTRACT(DAY FROM o.fecha) as OD, count(o.id_producto_ofertado) AS PC
FROM ofertas o join (categorias c JOIN productos p ON c.id = p.categoria) on o.id_producto_ofertado = p.id
GROUP BY  EXTRACT(DAY FROM o.fecha), c.nombre
)
GROUP BY CN

I suppose it works. But the result is a relation with 2 cols: CN & MAX(PC). If I try to bring the day (OD), because I need it, by using:

SELECT CN, MAX(PC), OD
FROM
(
SELECT c.nombre as CN, EXTRACT(DAY FROM o.fecha) as OD, count(o.id_producto_ofertado) AS PC
FROM ofertas o join (categorias c JOIN productos p ON c.id = p.categoria) on o.id_producto_ofertado = p.id
GROUP BY  EXTRACT(DAY FROM o.fecha), c.nombre
)
GROUP BY CN, OD

Then it return a 3 cols relation: CN, MAX(PC) & OD. BUT with the Category Name (CN) repeated, I think that because I need to group by OD in order to extract also the day. So, the return contains a row for each of the 31 days.

I just need a row for each category name, returning the category name, the number of the day of the month with the greatest number of offers and the greatest number of offers.

I'm stuck right now, cause I don't know what to do. Thanks

Upvotes: 1

Views: 217

Answers (1)

Andomar
Andomar

Reputation: 238058

You could use row_number to select the row with the highest offer count:

select  CustomerName
,       OrderDate
,       OfferCount
from    (
        select  CustomerName
        ,       OrderDate
        ,       OfferCount
        ,       row_number() over (partition by CustomerName
                                   order by OfferCount desc) as rn
        from    (
                select  c.nombre as CustomerName
                ,       EXTRACT(DAY FROM o.fecha) as OrderDate
                ,       count(o.id_producto_ofertado) as OfferCount
                FROM    ofertas o 
                join    categorias c 
                on      o.id_producto_ofertado = p.id
                join    productos p 
                on      c.id = p.categoria
                group by
                        c.nombre
                ,       EXTRACT(DAY FROM o.fecha)
                ) as GroupByDay
        ) as WithRowNumber
where   rn = 1

The subquery for row_number might not be necessary, but I haven't got a test Oracle nearby.

Upvotes: 2

Related Questions