[thelist] MYSQL - join a table, to itself?
Jason Handby
jason.handby at corestar.co.uk
Fri Sep 21 07:17:40 CDT 2007
Hi Tris,
> Ello all!
> I've got a table that captures users activity on a points
> spending site.
>
> my statement table has the follwoing fields:
>
> date, points, calcType, userId
>
> calcType is boolean, where 0 = - AND 1 = +
>
> What I've been asked to do is simply show 3 peices of data:
> Total points added
> Total poitns spent
> Total points left over...
>
> So, I've got a query
> SELECT SUM(points) as earned
> FROM `statement` as earnedTable
> WHERE
> earnedTable.calctype = 1
>
> But I'm convinced there's gotta be away to join the table with itself
> and get the calcType = 0 total too...
> the final query, I just want 2 fields.. I can work out the
> thrid myself...
>
> So, any ideas?
You should just be able to do this:
SELECT
(SELECT SUM(points) FROM `statement` WHERE calctype=1)
AS pointsEarned,
(SELECT SUM(points) FROM `statement` WHERE calctype=0)
AS pointsSpent
Jason
More information about the thelist
mailing list