On 2014-08-31, 9:35 PM, Darren Duncan wrote:
On 2014-08-31, 9:10 PM, jose isaias cabrera wrote:
SELECT cust, sum(ProjFund), sum(Xtra8), coalesce(billdate,bdate) as t FROM
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate
BETWEEN '2013-01-01' AND '2013-12-31' GROUP BY substr(t,1,7), cust;
I'm surprised that even compiles, since AFAIK the SELECT clause is evaluated
after the GROUP BY clause, and t doesn't exist yet at the GROUP BY stage (unless
you have another one in LSOpenProjects).
Maybe try using a subquery to force correct evaluation order?
Kind of like this:
SELECT cust, sum(ProjFund), sum(Xtra8), t FROM (
SELECT cust, ProjFund, Xtra8, coalesce(billdate,bdate) as t FROM
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate
BETWEEN '2013-01-01' AND '2013-12-31'
) x GROUP BY substr(t,1,7), cust;
-- Darren Duncan
Actually, isn't this more what you want?
SELECT cust, sum(ProjFund), sum(Xtra8), t FROM (
SELECT cust, ProjFund, Xtra8, substr(coalesce(billdate,bdate),1,7) as t FROM
LSOpenProjects WHERE billdate BETWEEN '2013-01-01' AND '2013-12-31' OR bdate
BETWEEN '2013-01-01' AND '2013-12-31'
) x GROUP BY t, cust;
-- Darren Duncan
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users