count, where question!

Hi!

Say I have mysql a table that has widgets in one column and price in another..

Is it possible to count the total number of widgets and the number of widgets that cost more than 10 in one query?

ie. count the same thing but with a different WHERE clause?

 

Something like this. You may need to adjust the syntax but this should work.

SELECT COUNT(widgets) AS count, (SELECT COUNT(widgets) FROM widgets_table WHERE widgets_cost > '10') AS count_10 FROM widgets_table;

Code (markup):

 

Oh hey, you're the same guy that solved my last problem, thanks!

I'd +rep you again but it says I have to spread it around first :\

 

Ok, I'm trying to use your code now + I think I'm doing something wrong. The query I've ended up with is quite a mouthful + is returning an error:

SELECT COUNT(DISTINCT mid) AS pos (SELECT COUNT (DISTINCT mid) AS tot FROM games_scores WHERE gid = 37 AND (datescored > 1207669542) ) FROM games_scores WHERE gid = 37 AND (datescored > 1207669542)  AND (score > 671 OR (score = 671 AND timespent < 26)) 

Code (markup):

Any ideas?

 

Try it like this. This may be easier to debug if it doesn't work.

SELECT 
(SELECT COUNT (DISTINCT mid) FROM games_scores WHERE gid = 37 AND (datescored > 1207669542) AND (score > 671 OR (score = 671 AND timespent < 26))) AS pos, 
(SELECT COUNT (DISTINCT mid) FROM games_scores WHERE gid = 37 AND (datescored > 1207669542)) AS scores

Code (markup):

 

The error message is now:

mySQL query error: SELECT 
(SELECT COUNT (DISTINCT mid) FROM games_scores WHERE gid = 37 AND (datescored > 1207672393)  AND (score > 671 OR (score = 671 AND timespent < 26))) AS pos, 
(SELECT COUNT (DISTINCT mid) FROM games_scores WHERE gid = 37 AND (datescored > 1207672393) ) AS scores

mySQL error: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT (DISTINCT mid) FROM games_scores WHERE gid = 37 AN
mySQL error code: 

Code (markup):

 

Try putting ' around the numbers in the where clause.

gid = '37'
datescored > '1207672393'
score > '671'
etc

 

Nope still no joy. I think it must be because I'm using MySQL4? Looks like I'll have to use seperate queries, I'm sure the syntax is right now but it still aint working :S