Monday, March 12, 2012

need quick help with a sql query

this query works, i want to add a 4th column that is the value of the 3rd column subracted from the value of the 2nd column, how can i add this??

SELECT `tagid` AS w1, (

SELECT count( `value` )
FROM tags
WHERE `value` =1
AND `tagid` = w1
) AS w2, (

SELECT count( `value` )
FROM tags
WHERE `value` =0
AND `tagid` = w1
) AS w3
FROM tags
WHERE `value` > -1
GROUP BY `tagid`
ORDER BY w2 DESC

you cant reference those columns can you? like this

well you can do another subquery like you already did and then add all the logic to get columns 2 and 3 then there you go

SELECT `tagid` AS w1, (SELECT count( `value` )FROM tagsWHERE `value` =1AND `tagid` = w1) AS w2,
(SELECT count( `value` )FROM tagsWHERE `value` =0AND `tagid` = w1) AS w3,
((SELECT count( `value` )FROM tagsWHERE `value` =1AND `tagid` = w1) -(SELECT count( `value` )FROM tagsWHERE `value` =0AND `tagid` = w1) ) as w4
FROM tags
WHERE `value` > -1
GROUP BY `tagid`
ORDER BY w2 DESC

No comments:

Post a Comment