COUNTING - Help

[Canman2005]

Hi all

 

Wonder if someone can help.

 

I have the following QUERY which contains a COUNT statement

 

SELECT u.id , COUNT(*) AS `total` FROM log l JOIN users u ON l.userid = u.id WHERE u.place = 1 GROUP BY u.id ORDER BY `total` DESC

 

this works great and returns a result such as

 

1   877
2   455
3   232
4   199

 

but is it possible to do a WHERE statement in my COUNT, so it would look something like

 

COUNT(*) WHERE l.type1 = 1 || l.type2 = 1

 

 

but then also include another COUNT statement which would look like

 

COUNT(*) WHERE l.type3 = 1

 

but multiple the above COUNT number by 2, I know it's totally wrong, but something like

 

COUNT(*) WHERE l.type3 = 1 * 2

 

 

but then ADD the numbers from the 1st COUNT and 2nd COUNT together and output them as the

 

ORDER BY `total` DESC

 

value

 

so it would look something like

 

1   ((COUNT 2 * 2) + COUNT 1)
2   ((COUNT 2 * 2) + COUNT 1)
3   ((COUNT 2 * 2) + COUNT 1)
4   ((COUNT 2 * 2) + COUNT 1)

 

Any help would be great, been trying to crack this for the last few hours but having no luck

[Canman2005]

I managed with

 

SELECT u.id, l.type1,l.type2,l.type3, (select Count(*) from log l WHERE l.type1 = 1 || l.type2 = 1) AS tot1,(select Count(*) from log l WHERE l.type3 = 1) AS tot2 FROM log l JOIN users u ON l.userid = u.id WHERE u.place = 1 GROUP BY u.id

 

to output

 

id   type1   type2   type3   tot1   tot2
1   1           1         1           233   244
2   1           1         1           200   201
3   1           1         1           123   143
4   1           1         1           89      99
5   1           1         1           2        4

 

which is excellent

 

is it possible to add up "tot1" and "tot2" and output them as "total"

[Canman2005]

Sorted, thanks anyway to anyone reading this