MySql Query to find all part numbers used and quatity for a give time

Using MySQL and PHP.
Example Database:  A list of parts that have been sold.

I need to be able to query the database to find all partnumbers sold by todays date and the total sold of each part number. Example:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

COUNT(*) as 'Amount Sold Today'



GROUP BY `PartNumber`

ORDER BY `PartNumber`

I think that's what you need. That will give you a list of all parts sold today, with the number sold next to them.

I'm not sure what you mean when you say "sold by todays date", the solution above will give you any item sold before or on todays date, this is done with the WHERE clause:

If you wanted everything before todays date, you would take the equals (=) sign out.

If you require an explanation of what the above SQL is doing then feel free to ask, i.e so you can expand it as you need. I wont in this post as it could just confuse things.

Matt Ayers
mustang_keithAuthor Commented:
Updated database structure:

Ok this is mysql statement:
SELECT 'part_number', COUNT(*) as 'Quantity'
FROM production
WHERE DATE=20081113
GROUP BY 'part_number'
ORDER BY 'part_number'

It is returning:
part_number  215

Where 215 above is the total parts sold.  Its not breaking out the individual part numbers.
mustang_keithAuthor Commented:
Update query:
SELECT part_number, COUNT(*) as 'Quantity'
FROM production
WHERE DATE=20081113
GROUP BY 'part_number'
ORDER BY 'part_number'

Had to take out the '' around part_number after SELECT
It looks like you've typed my above query wrongly. If you copy and paste it exactly it should be ok.

What you've mistyped is the quotes. There are 2 types in MySQL:
' '  OR " " = String
` ` = Field /Table / Database / Schema declaration. - These are called backticks or identifiers I believe.

The quotes are very important. You could take them out in your case but I prefer to keep it everywhere needed. You need to do the same for the GROUP and ORDER  bit.

MySQL Reference of backticks: 

Also you've changed the date comparison, which wont work unless you have that column in your table. I'd leave it as below as it'll run without change everyday. Or if you do want it for a fixed date do:

Replace DD with the day, MM with the Month, YYYY with the year.

The DATE() function converts the given string to a MySQL date for accurate comparison, otherwise you can have some funny things happen like on leap year etc.

SQL without backticks and date change:
COUNT(*) as 'Quantity'



GROUP BY PartNumber

ORDER BY PartNumber

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.