Can't find what's wrong with this query

edusilva · #1 (**permalink**) 05-04-09, 16:30

Hello, I'm not very familiar with the SQL language and I'm using Pervasive Data Manage to build a query.
What I want to achieve is this:
In Sage Business Visipon there is one table called CUSTOMER and another called SALES_HISTORY_HEADER. I want to know the how many orders a customer have placed in our system, for customers whose last order was before Nov. 31, 2008
I have this query:

Code:

SELECT "CUSTOMER"."CUS_NO", "CUSTOMER"."LAST_DATE", COUNT("SALES_HISTORY_HEADER"."NUMBER") AS "TOTALORDERS" 
FROM "CUSTOMER" INNER JOIN "SALES_HISTORY_HEADER"
ON  "CUSTOMER"."CUS_NO" = "SALES_HISTORY_HEADER"."CUST_NO"
WHERE "CUSTOMER"."LAST_DATE" < '20081131'
GROUP BY "CUSTOMER"."CUS_NO"

But then I get this error message: ODBC Error: SQLSTATE = 37000, Native error code = 0
Column invalid. Must be a group by column: LAST_DATE in SELECT LIST.
If I change the group by line with this:

Code:

GROUP BY "CUSTOMER"."LAST_DATE"

ODBC Error: SQLSTATE = 37000, Native error code = 0
Column invalid. Must be a group by column: CUS_NO in SELECT LIST.

Could anybody help me with this syntax, please?

mirtheil · #2 (**permalink**) 05-04-09, 16:46

I haven't spent a lot of time on this but I think this might work:

Quote:

SELECT "CUSTOMER"."CUS_NO", "CUSTOMER"."LAST_DATE", COUNT("SALES_HISTORY_HEADER"."NUMBER") AS "TOTALORDERS"
FROM "CUSTOMER" INNER JOIN "SALES_HISTORY_HEADER"
ON "CUSTOMER"."CUS_NO" = "SALES_HISTORY_HEADER"."CUST_NO"
WHERE "CUSTOMER"."LAST_DATE" < '20081131'
GROUP BY "CUSTOMER"."CUS_NO", "CUSTOMER"."LAST_DATE"