Results 1 to 4 of 4
  1. #1
    Join Date
    May 2009
    Posts
    3

    Unanswered: Query with Counts Help...

    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!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT s.name
         , p.descr
         , o.counts
      FROM ( SELECT StateId 
                  , PartNumber
                  , COUNT(*) AS counts
               FROM original_table 
             GROUP
                 BY StateId 
                  , PartNumber ) AS o
    INNER
      JOIN states AS s
        ON s.StateId = o.StateId
    INNER
      JOIN parts AS p
        ON p.PartNumber = o.PartNumber
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2009
    Posts
    3
    Thanks much for that!!! I'll give it a try at work tomorrow, but I think that is what I was looking for.


  4. #4
    Join Date
    May 2009
    Posts
    3
    Thanks again r937. That basically worked for me. I had to tweak it a bit as I didn't really give you the correct stuff I needed, but it worked all the same!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •