How use a group by in a sub query

  • Thread starter Thread starter Nuno Gomes
  • Start date Start date
N

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
 
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.
Click to expand...
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 <--
 
Back
Top