Using the COUNT function with a subquery in SQL

Expand Post »

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

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?

Similar Threads

PLS I NEED YOUR HELP! Help on Reading and Editing of already Written CODE on php! in PHP

CodeBoy101

Reputation Points: 8

Solved Threads: 0

Junior Poster in Training

Offline

71 posts
since Dec 2007

Permalink

Dec 6th, 2008

Re: Using the COUNT function with a subquery in SQL

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:

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;

Last edited by darkagn; Dec 6th, 2008 at 6:26 pm.

darkagn

Reputation Points: 395

Solved Threads: 192

Veteran Poster

Offline

1,136 posts
since Aug 2007

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MySQL Forum Timeline: Database Design Question

Next Thread in MySQL Forum Timeline: Help with SQL Query

DaniWeb Message

Cancel Changes

User Name
Password