By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,028 Members | 1,869 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,028 IT Pros & Developers. It's quick & easy.

Query with Counts Help...

P: 2
Hello All,

I need help with a SQL Query.

I have a table with a StateId and PartNumber. I'll be joining some tables to get the state name from the StateId and Part Description from the Part Number.

Where I need the help is from the original table. What I have to do is list the part and quantity sold in each state.

I might have something like...

StateId PartNumber
19 123
18 456
19 123
18 789

I need a query to get the counts for each part in each state so I get something like...

19 123 2
18 456 1
18 789 1

So after all my joins and stuff I would get
Arkansas Misc Part Example Description 2
Alabama Another Part Example 1
Alabama My Test Part Description 1

After all is said and done, I'll sort by state then by description.....

Alabama Another Part Example 1
Alabama My Test Part Description 1
Arkansas Misc Part Example Description 2

I think I can join to get the state names and part descriptions, but I don't know how to get the counts appropriately.

Any help would be greatly appreciated!

Thanks!
May 17 '09 #1
Share this Question
Share on Google+
2 Replies


ck9663
Expert 2.5K+
P: 2,878
follow this code:

Expand|Select|Wrap|Line Numbers
  1.  
  2. select stateneme, partdescription, count(*)
  3. from YourMainTable M
  4. left join YourStateTable  st on st.stateid = m.stateid
  5. left join YourPartTable pt on pt.partnumber = m.partnumber
  6. group by statename, partdescription
  7. order by 2
  8.  
  9.  
I don't mean that you follow it literally. Just follow the logic.

Happy Coding!

--- CK
May 18 '09 #2

P: 2
Thanks! That Helped!!
May 21 '09 #3

Post your reply

Sign in to post your reply or Sign up for a free account.