Hi Jose:
In SQL, aggregate function cannot be used in the WHERE clause. Use the HAVING
clause instead.
Try this:
SELECT cust,
ProjID,
proj,
A_No,
bdate,
CASE Xtra4 WHEN 'y' THEN vEmail ELSE 'noemail' END,
sum(ProjFund),
sum(ProjFund)-sum(CASE Xtra4 WHEN 'y' THEN invoice ELSE 0 END),
sum(CASE Xtra4 WHEN 'y' THEN invoice ELSE 0 END)
FROM LSOpenJobs
WHERE
lower(cust) = 'phin'
AND PClass='Language'
AND (bdate BETWEEN '2014-01-15' AND '2014-05-15')
GROUP BY
cust, ProjID, proj, A_No;
HAVING
sum(case Xtra4 when 'y' then invoice else 0 end) > 0
-----Original Message-----
From: jose isaias cabrera <cabr...@wrc.xerox.com>
To: General Discussion of SQLite Database <sqlite-users@sqlite.org>
Sent: Thu, May 29, 2014 2:18 pm
Subject: [sqlite] SELECTing WHERE calculation > 0
Greetings!
I have this select,
SELECT cust,
ProjID,
proj,
A_No,
bdate,
CASE Xtra4 WHEN 'y' THEN vEmail ELSE 'noemail' END,
sum(ProjFund),
sum(ProjFund)-sum(CASE Xtra4 WHEN 'y' THEN invoice ELSE 0 END),
sum(CASE Xtra4 WHEN 'y' THEN invoice ELSE 0 END)
from LSOpenJobs
WHERE
lower(cust) = 'phin' AND
PClass='Language' AND
sum(case Xtra4 when 'y' then invoice else 0 end) > 0 AND
(bdate BETWEEN '2014-01-15' AND '2014-05-15')
GROUP BY cust, ProjID, proj, A_No;
It complains with
Error: misuse of aggregate: sum()
because of the WHERE clause "sum(case Xtra4 when 'y' then invoice else 0
end) > 0". If I take it out, the SELECT works accordingly. But, what I
would like to SELECT is just any records where this "sum(case Xtra4 when 'y'
then invoice else 0 end) > 0. Is this a possibility? Thanks.
josé
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
