?
Solved

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

Posted on 2008-11-13
6
Medium Priority
?
202 Views
Last Modified: 2013-12-13
Using MySQL and PHP.
Example Database:  A list of parts that have been sold.
ID,PartNumber,DateSold,TimeSold,Description

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:
PartC,65
PartZ,21
PartF,105
PartD,1
0
Comment
Question by:mustang_keith
  • 2
  • 2
4 Comments
 
LVL 1

Expert Comment

by:mattayers08
ID: 22949391
SELECT
`PartNumber`,
COUNT(*) as 'Amount Sold Today'

FROM
database_goes_here

WHERE
DATE(`DateSold`)<=DATE(NOW())

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:
DATE(`DateSold`)<=DATE(NOW())

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.

Regards,
Matt Ayers
0
 

Author Comment

by:mustang_keith
ID: 22949681
Updated database structure:
id,part_number,date,time,extra1

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.
0
 

Author Comment

by:mustang_keith
ID: 22949729
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
0
 
LVL 1

Accepted Solution

by:
mattayers08 earned 2000 total points
ID: 22950601
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:
http://dev.mysql.com/doc/refman/5.0/en/identifiers.html 

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:
WHERE
DATE(DateSold) = DATE('YYYYMMDD')

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:
SELECT
PartNumber,
COUNT(*) as 'Quantity'

FROM
database_goes_here

WHERE
DATE(DateSold)=DATE(NOW())

GROUP BY PartNumber

ORDER BY PartNumber
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this. Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it i…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses
Course of the Month14 days, 8 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question