MySQL

Using the COUNT function with a subquery in SQL

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Dec 2007

Posts: 65

Reputation: CodeBoy101 is an unknown quantity at this point

Solved Threads: 0

CodeBoy101 CodeBoy101 is offline

Offline

Junior Poster in Training

Using the COUNT function with a subquery in SQL

Dec 6th, 2008

Hi I'm having trouble with a subquery and really need some help.
I'm trying to count to count the number of beats sold by a particular producer.

So the first thing I did was SELECT all the beats sold by that producer so I wrote the code

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
 
SELECT name, production_no
FROM beats
WHERE availability = 'Sold'
AND p_staffid = 1;
select name, production_no
from beats
where availability = 'Sold'
and p_staffid = 1;

'p_staffid' is the id number for the particular producer and the 'production_no' is the primary key for the beat itself. I have tested this code and it works perfectly.

But how do I count the results?

•

Join Date: Aug 2007

Posts: 807

Reputation: darkagn has a spectacular aura about

Solved Threads: 110

darkagn

Offline

Practically a Posting Shark

Re: Using the COUNT function with a subquery in SQL

Dec 6th, 2008

The COUNT function will return the number of rows returned by the query which is what I think you are trying to do. The COUNT function requires a column to count as a parameter, so your query will look something like this:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
SELECT COUNT(name) as number_sold
FROM beats
WHERE availability = 'Sold'
AND p_staffid = 1;
select COUNT(name) as number_sold
from beats
where availability = 'Sold'
and p_staffid = 1;