Requête imbriquée, max() et date

La solution étant 253...
Démonstration :

SELECT day AS D, sum(number) AS Volume
FROM counting
WHERE month = '03'
AND year = 2004
AND number > 0
AND category LIKE 'A1%
AND (category <> 'A120' OR category = 'C186'
OR category = 'C187'
OR category = 'C188'
OR category = 'C189'
OR category = 'B186'
OR category = 'B187'
OR category = 'B188'
OR category = 'B189'
OR category = 'C196'
OR category = 'C197'
OR category LIKE 'G1%')
AND site = 09
GROUP BY day
HAVING sum(number) = 253

remplacement du 253 par la requête primale :

SELECT day AS D, sum(number) AS Volume
FROM counting
WHERE month = '03'
AND year = 2004
AND number > 0
AND category LIKE 'A1%
AND (category <> 'A120' OR category = 'C186'
OR category = 'C187'
OR category = 'C188'
OR category = 'C189'
OR category = 'B186'
OR category = 'B187'
OR category = 'B188'
OR category = 'B189'
OR category = 'C196'
OR category = 'C197'
OR category LIKE 'G1%')
AND site = 09
GROUP BY day
HAVING sum(number) = (SELECT max(Volume)
FROM (SELECT day AS D, sum(number) AS Volume
FROM counting
WHERE (month = '03')
AND (year = 2004)
AND (number > 0)
AND (category LIKE 'A1%'
AND category <> 'A120'
OR category = 'C186'
OR category = 'C187'
OR category = 'C188'
OR category = 'C189'
OR category = 'B186'
OR category = 'B187'
OR category = 'B188'
OR category = 'B189'
OR category = 'C196'
OR category = 'C197'
OR category LIKE 'G1%'
)
AND site = 09
GROUP BY day
)
)



A +

Kira a écrit:

Bonjour à tous,

J'essaye de déterminer le pic d'achat (par jour) sur une période pour un
point de vente donné, selon certaines catégories d'objets vendus.

Il faut donc faire une sous-requête pour additionner les objets
m'intéressant, puis les grouper par jour.
J'effectue ensuite une requête me permettant d'obtenir le nombre le plus
grand de la première requête.

J'y suis arrivé, mais j'arrive uniquement à connaître ce volume, et non
la date qui lui correspond.

Voici ma requête, ne retournant que le volume :

SELECT max(Volume)
FROM (SELECT day AS D, sum(number) AS Volume
FROM counting
WHERE (month = '03')
AND (year = 2004)
AND (number > 0)
AND (category LIKE 'A1%'
AND category <> 'A120'
OR category = 'C186'
OR category = 'C187'
OR category = 'C188'
OR category = 'C189'
OR category = 'B186'
OR category = 'B187'
OR category = 'B188'
OR category = 'B189'
OR category = 'C196'
OR category = 'C197'
OR category LIKE 'G1%'
)
AND site = 09
GROUP BY day
)


Cela me retourne 253 (pour le 3 du mois). Je recherche donc un moyen de
faire :

D | Volume
3 253

Merci d'avance pour votre aide.

--
Frédéric BROUARD, MVP Microsoft SQL Server. Langage SQL / Delphi / web
Livre SQL - col. Référence : http://sqlpro.developpez.com/bookSQL.html
Le site du SQL, pour débutants et pros : http://sqlpro.developpez.com
****************** mailto:brouardf@club-internet.fr ******************