How to use GROUP BY statement in Advanced SQL
octoniJul 6 2011 — edited Jul 6 2011Hi there,
I create a query contains UNION ALL operation of two different criteria, e.g : sales by brand a and sales by brand b. And i want to display the two measurement side by side in the same row.
The original query is :
SELECT saw_0, saw_1, saw_2
FROM ((SELECT "Dim Periods"."Week Year" saw_0, measures.sales saw_1,
0 saw_2
FROM "Sales Report"
WHERE "Dim Brand"."Product Group Desc" =
'BRAND-A')
UNION ALL
(SELECT "Dim Periods"."Week Year" saw_0, 0 saw_1,
measures.sales saw_2
FROM "Sales Report"
WHERE "Dim Brand"."Product Group Desc" = 'BRAND B')) t1
Then, I try to add SUM aggregation and GROUP BY , such query belows :
SELECT saw_0, SUM (saw_1), SUM (saw_2)
FROM ((SELECT "Dim Periods"."Week Year" saw_0, measures.sales saw_1,
0 saw_2
FROM "Sales Report"
WHERE "Dim Brand"."Product Group Desc" =
'BRAND-A')
UNION ALL
(SELECT "Dim Periods"."Week Year" saw_0, 0 saw_1,
measures.sales saw_2
FROM "Sales Report"
WHERE "Dim Brand"."Product Group Desc" = 'BRAND B')) t1
GROUP BY saw_0
but it raise error [nQSError: 59111] The SQL statement must include a GROUP BY clause .
Any idea how to solve this ?
thanks