Query with Counts Help...

[Superfreak31]

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!

[ck9663]

follow this code:

 
select stateneme, partdescription, count(*)
from YourMainTable M
left join YourStateTable  st on st.stateid = m.stateid
left join YourPartTable pt on pt.partnumber = m.partnumber
group by statename, partdescription
order by 2
 
 

I don't mean that you follow it literally. Just follow the logic.

Happy Coding!

--- CK

[Superfreak31]

Thanks! That Helped!!