How use a group by in a sub query

[Nuno Gomes]

Hello,

I use the query:
SELECT MONTH(P.DOC_DATE) MONTH_, COUNT(*) CUSTOMERS_ATL,
(
SELECT SUM(STAT_CAISSE_J.STAT_VALEUR9)
FROM STAT_CAISSE_J
WHERE (CONVERT(CHAR, STAT_CAISSE_J.STAT_DATE, 112) >= '20090101'
AND CONVERT(CHAR, STAT_CAISSE_J.STAT_DATE, 112) <='20091231')
AND STAT_CAISSE_J.STAT_TYPE=1
)-COUNT(*) CUSTUMERS_VND

FROM ATL_DOCUMENT P

WHERE (P.DOC_TYPE=14 OR P.DOC_TYPE=15)
AND (CONVERT(CHAR, P.DOC_DATE, 112) >= '20090101'
AND CONVERT(CHAR, P.DOC_DATE, 112) <='20091231')

GROUP BY MONTH(P.DOC_DATE)

And the result is:
MONTH_ CUSTOMERS_ATL CUSTUMERS_VND
----------- ------------- -----------------------------------------------------
7 77 417.0
8 4 490.0
12 2 492.0


The month_ and customers_atl are ok.
But the customer_vnd is not ok because the main query have a GROUP BY clause
and the subquery don't made the group by.

How can this could be made?


Thanks,
Nuno

 

[Stefan Hoffmann]

hi Nuno,

The month_ and customers_atl are ok.
But the customer_vnd is not ok because the main query have a GROUP BY clause
and the subquery don't made the group by.

The subquery is not aware of any outer clause if you don't specify it.
afaik you need at least to filter the subquery:

SELECT MONTH(O.[DOC_DATE]) AS [MONTH_],
COUNT(*) AS [CUSTOMERS_ATL],
(
SELECT SUM(I.[STAT_VALEUR9])
FROM [STAT_CAISSE_J] I
WHERE (CONVERT(CHAR, I.[STAT_DATE], 112) >= '20090101'
AND CONVERT(CHAR, I.[STAT_DATE], 112) <='20091231')
AND I.[STAT_TYPE] = 1
AND MONTH(I.[whatFieldEver]) = MONTH(O.DOC_DATE)
) - COUNT(*) AS [CUSTUMERS_VND]

FROM [ATL_DOCUMENT] O

WHERE (O.[DOC_TYPE] = 14 OR O.[DOC_TYPE] = 15)
AND CONVERT(CHAR, O.[DOC_DATE], 112) >= '20090101'
AND CONVERT(CHAR, O.[DOC_DATE], 112) <='20091231'

GROUP BY MONTH(O.DOC_DATE)


mfG
--> stefan <--