SQL Group Where Clause

[Andy Trezise]

I want to select all customers from a SQL table along with a sum of all
their invoices but I'm having trouble using the where / group by clause

My statement reads

SQLEXEC(nSQL,"SELECT t1.*,SUM(t2.total_amount) AS total FROM CUSTOMERS as t1
left join invoices AS t2 ON (t1.account_code=t2.account_code) group by
t1.account_code","MY_RECS")

This works OK but as soon as I add a WHERE clause it fails...

SQLEXEC(nSQL,"SELECT t1.*,SUM(t2.total_amount) AS total FROM CUSTOMERS as t1
left join invoices AS t2 ON (t1.account_code=t2.account_code) group by
t1.account_code WHERE total > 1000","MY_RECS")

I've tried rearranging the statement but nothing seems to work.

[Dan Freeman]

I want to select all customers from a SQL table along with a sum of all their
invoices but I'm having trouble using the where / group by clause
My statement reads
SQLEXEC(nSQL,"SELECT t1.*,SUM(t2.total_amount) AS total FROM CUSTOMERS as t1
left join invoices AS t2 ON (t1.account_code=t2.account_code) group by
t1.account_code","MY_RECS")
This works OK but as soon as I add a WHERE clause it fails...
SQLEXEC(nSQL,"SELECT t1.*,SUM(t2.total_amount) AS total FROM CUSTOMERS as t1
left join invoices AS t2 ON (t1.account_code=t2.account_code) group by
t1.account_code WHERE total > 1000","MY_RECS")
I've tried rearranging the statement but nothing seems to work.

I think you want HAVING instead of WHERE.

Dan

[Andy Trezise]

That's done the trick...thanks

Post by Dan Freeman

Post by Andy Trezise
I want to select all customers from a SQL table along with a sum of all
their invoices but I'm having trouble using the where / group by clause
My statement reads
SQLEXEC(nSQL,"SELECT t1.*,SUM(t2.total_amount) AS total FROM CUSTOMERS as
t1 left join invoices AS t2 ON (t1.account_code=t2.account_code) group by
t1.account_code","MY_RECS")
This works OK but as soon as I add a WHERE clause it fails...
SQLEXEC(nSQL,"SELECT t1.*,SUM(t2.total_amount) AS total FROM CUSTOMERS as
t1 left join invoices AS t2 ON (t1.account_code=t2.account_code) group by
t1.account_code WHERE total > 1000","MY_RECS")
I've tried rearranging the statement but nothing seems to work.

I think you want HAVING instead of WHERE.
Dan

[Gene Wirchenko]

On Thu, 27 May 2010 00:04:19 +0100, "Andy Trezise"

Post by Andy Trezise
I want to select all customers from a SQL table along with a sum of all
their invoices but I'm having trouble using the where / group by clause
My statement reads
SQLEXEC(nSQL,"SELECT t1.*,SUM(t2.total_amount) AS total FROM CUSTOMERS as t1
left join invoices AS t2 ON (t1.account_code=t2.account_code) group by
t1.account_code","MY_RECS")
This works OK but as soon as I add a WHERE clause it fails...
SQLEXEC(nSQL,"SELECT t1.*,SUM(t2.total_amount) AS total FROM CUSTOMERS as t1
left join invoices AS t2 ON (t1.account_code=t2.account_code) group by
t1.account_code WHERE total > 1000","MY_RECS")
I've tried rearranging the statement but nothing seems to work.

Use a having clause instead of a where clause.

The where clause is used when initially selecting rows. The
having clause is used with the results of aggregates (such as sum()).

Sincerely,

Gene Wirchenko