Re: [sqlite] SELECTing WHERE calculation > 0

2014-05-30 Thread jose isaias cabrera 


"Edward Lau" wrote...


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



Thanks. 


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SELECTing WHERE calculation > 0

2014-05-30 Thread jose isaias cabrera 


"Richard Hipp" wrote...


Do you want a HAVING clause?


Thanks.  Didn't know about this one. Now it works:

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;




On Thu, May 29, 2014 at 5:14 PM, jose isaias cabrera 


wrote:



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





--
D. Richard Hipp
d...@sqlite.org
___
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

Re: [sqlite] SELECTing WHERE calculation > 0

2014-05-29 Thread Edward Lau 
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

Re: [sqlite] SELECTing WHERE calculation > 0

2014-05-29 Thread Richard Hipp 
Do you want a HAVING clause?


On Thu, May 29, 2014 at 5:14 PM, jose isaias cabrera 
wrote:

>
> 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
>



-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SELECTing WHERE calculation > 0

2014-05-29 Thread jose isaias cabrera 


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