Re: [sqlite] Aggregate From Two-Table SELECT

[Rich Shepard]

On Thu, 18 Feb 2010, P Kishor wrote:

> SELECT l.llid, l.name, SUM(s.endKM - s.beginKM) AS distance
> FROM lotic AS l JOIN streamlength AS s ON l.llid = s.llid
> WHERE l.llid = '1226038453652'
> GROUP BY l.llid, l.name

   Thank you. Now I know.

Much appreciated,

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

Re: [sqlite] Aggregate From Two-Table SELECT

[P Kishor]

On Thu, Feb 18, 2010 at 1:06 AM, Rich Shepard  wrote:
>   I'd appreciate learning how to correctly write a SELECT statement that
> reports the SUM of one returned column.
>
>   I can select all relevant rows, but don't know where to put the
> SUM(distance) phrase:
>
>   SELECT l.llid, l.name, s.endKM - s.beginKM AS distance
>        FROM lotic AS l, streamlength AS s
>                WHERE l.llid = s.llid and l.llid = '1226038453652';
>
>   I would like SQL to do the work of summing the derived column 'distance'
> for me.
>

SELECT l.llid, l.name, SUM(s.endKM - s.beginKM) AS distance
FROM lotic AS l JOIN streamlength AS s ON l.llid = s.llid
WHERE l.llid = '1226038453652'
GROUP BY l.llid, l.name



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



-- 
Puneet Kishor http://www.punkish.org
Carbon Model http://carbonmodel.org
Charter Member, Open Source Geospatial Foundation http://www.osgeo.org
Science Commons Fellow, http://sciencecommons.org/about/whoweare/kishor
Nelson Institute, UW-Madison http://www.nelson.wisc.edu
---
Assertions are politics; backing up assertions with evidence is science
===
Sent from Brussels, Brussels-Capital Region, Belgium
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Aggregate From Two-Table SELECT

[Rich Shepard]

   I'd appreciate learning how to correctly write a SELECT statement that
reports the SUM of one returned column.

   I can select all relevant rows, but don't know where to put the
SUM(distance) phrase:

   SELECT l.llid, l.name, s.endKM - s.beginKM AS distance
FROM lotic AS l, streamlength AS s
WHERE l.llid = s.llid and l.llid = '1226038453652';

   I would like SQL to do the work of summing the derived column 'distance'
for me.

Rich

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