SQL Question

[partyman66]

Hey,
I was wondering if anyone could help me out with a mySQL question.

I am trying to make a querry to my database that will populate the rows of an output table with the results.

I am getting all of the values for my table from a single mySQL querry and I can't seem to figure out how to do a Count on a specific field in my Database Table where the field has a certain value only.

Example: I want my querry to go to the database and get info about a bunch of stuff in addition to getting a count for the field in the DB called "WLT" but only where it has the value 'W'.


I tried the following:

SELECT
Teams.TEAM_NAME AS TEAM_NAME1,
(COUNT(TEAM_STATS.WLT = 'W')) AS WIN,
(COUNT(TEAM_STATS.WLT = 'L')) AS LOSS,
(COUNT(TEAM_STATS.WLT = 'T')) AS TIE,
((COUNT(TEAM_STATS.WLT = 'W') + ((COUNT(TEAM_STATS.WLT = 'T'))*.5)) / COUNT(*)) AS WIN_PERC,
TEAM_STATS.TEAM_ID
FROM Teams RIGHT JOIN TEAM_STATS ON Teams.TEAM_ID = TEAM_STATS.TEAM_ID
GROUP BY TEAM_ID

For some reason...the line "(COUNT(TEAM_STATS.WLT = 'W')) AS WIN," just gets a count of all the values in the field throughout the DB, rather than just the ones where it equals 'W'.

Does anyone know how i could change this to make it work?
Can you embed mySQL commands within others?..because if so, i was thinking I could just replace the above line with a get on all values WHERE it equals 'W', then do a Count on that.

Any help would be greatly appreciated!

Thanks in advance

[dgobrien]

I did it with subqueries--with my own data. I didn't have time to model your tables, but here's the gist of it...

select teams.team_name, w.cnt, l.cnt, t.cnt
from
(select a.team_id,
cnt = (select count(*) from team_stats b
where wlt = 'W' and b.team_id=a.team_id)
from team_stats a
group by team_id) as w,
(select a.team_id,
cnt = (select count(*) from team_stats b
where wlt = 'L' and b.team_id=a.team_id)
from team_stats a
group by team_id) as l,
(select a.team_id,
cnt = (select count(*) from team_stats b
where wlt = 'T' and b.team_id=a.team_id)
from team_stats a
group by team_id) as t,
teams
where teams.team_id = w.team_id
and w.team_id = l.team_id
and l.team_id = t.team_id

I wasn't able to test the syntax, but the idea is to use sub-queries to create small memory tables containing the aggregate wins, losses, etc. and join them with the teams table.

Let me know how it goes.