SitePoint Sponsor

User Tag List

Results 1 to 16 of 16
  1. #1
    SitePoint Evangelist NokX's Avatar
    Join Date
    Feb 2003
    Location
    Knoxville, TN
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting all and listing a count

    i have two tables. one table contains all the states and the other table contains a list of vendors.

    i want a list of all the states with the number of vendor(s) in each state next to each state name. for example...

    Alabama (3 vendors)
    Alaska (1 vendor)
    Arizona (6 vendors)
    etc...

    here's what i have now:

    (s.Name = full state name, s.Value = abbreviation of the state name)

    SELECT s.Name FROM tblVendors AS v RIGHT JOIN tblStates AS s ON v.State = s.Value

    this gets me all the states (and makes multiple records of each state where there are vendors in that state) - which is a start, but i'm wanting to simply have a count next to each state name.

    and this is probably something i'll have to group together when i output the results in php. and i know how to do that, but i'd like to knock it all out with one query instead of using php to group and count it all.

  2. #2
    SitePoint Enthusiast
    Join Date
    Jun 2005
    Posts
    92
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi
    do you mean this?
    Code:
    SELECT s.Name , count(*)
    FROM tblVendors AS v 
    RIGHT JOIN tblStates AS s 
    ON v.State = s.Value
    group by s.state

  3. #3
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select
        s.name
      , count(*)
    from tblStates s
    join tblVendors v on
      v.state = s.value
    group by s.name

  4. #4
    SitePoint Evangelist NokX's Avatar
    Join Date
    Feb 2003
    Location
    Knoxville, TN
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yup - that's it! thank you! i tried the count() earlier, but i had specified a particular field such as...

    count(v.VendorID) instead of count(*) and i left the "group by" off. thank you!

  5. #5
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by NokX
    yup - that's it! thank you! i tried the count() earlier, but i had specified a particular field such as...

    count(v.VendorID) instead of count(*) and i left the "group by" off. thank you!
    Actually, using count(v.VendorID) should work fine. Also, you should use left join so if there is a state with cero vendors, it won't be ignored.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  6. #6
    SitePoint Evangelist NokX's Avatar
    Join Date
    Feb 2003
    Location
    Knoxville, TN
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Toly
    Actually, using count(v.VendorID) should work fine. Also, you should use left join so if there is a state with cero vendors, it won't be ignored.
    yeah - i'm trying to work out a little issue with that right now. every state has a 1 for the count - but what sucks is i need to show (0 vendors) for ones with none, but if the vendor has 1, it shows as one.

  7. #7
    SitePoint Evangelist NokX's Avatar
    Join Date
    Feb 2003
    Location
    Knoxville, TN
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    duh - i was leaving it out of the grouping...

    SELECT s.Name, s.Value, COUNT(v.VendorID) AS c
    FROM tblVendors AS v RIGHT JOIN tblStates AS s ON v.State = s.Value
    GROUP BY s.Name, s.Value, v.VendorID

  8. #8
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by NokX
    but if the vendor has 1, it shows as one.
    What do you mean by this?

    Here's the same query longneck posted but using what I mentioned in my last post...

    Code:
    select
        s.name
      , count(v.VendorID) as total
    from tblStates s
    left join tblVendors v on
      s.value = v.state
    group by s.name
    That should work fine.
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  9. #9
    Free your mind Toly's Avatar
    Join Date
    Sep 2001
    Location
    Panama
    Posts
    2,181
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by NokX
    duh - i was leaving it out of the grouping...

    SELECT s.Name, s.Value, COUNT(v.VendorID) AS c
    FROM tblVendors AS v RIGHT JOIN tblStates AS s ON v.State = s.Value
    GROUP BY s.Name, s.Value, v.VendorID
    You don't need to group v.VendorID and use a LEFT JOIN instead...

    Code:
    select
        s.Name
      , s.Value
      , count(v.VendorID) as c
    from tblStates s
    left join tblVendors v on
      s.Value = v.State
    group by s.Name, s.Value
    Community Guidelines | Community FAQ

    "He that is kind is free, though he is a slave;
    he that is evil is a slave, though he be a king." - St. Augustine

  10. #10
    SitePoint Evangelist NokX's Avatar
    Join Date
    Feb 2003
    Location
    Knoxville, TN
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SELECT s.Name, s.Value, Count(*) AS c
    FROM tblVendors AS v RIGHT JOIN tblStates AS s ON v.State = s.Value
    WHERE v.Active = Yes
    GROUP BY s.Name, s.Value


    this does what i need, except it's leaving out any state that doesn't have a vendor. i've tried FROM tblStates AS s LEFT JOIN tblVendors AS v ON v.State = s.Value, as well

    Results
    Name Value c
    California
    CA 3
    Connecticut
    CT 2
    Georgia
    GA 1
    Illinois
    IL 5
    Indiana IN 1
    Michigan MI 1
    Montana
    MT 1
    New Jersey NJ 1
    North Carolina NC 1
    Ohio
    OH 1
    Tennessee
    TN 35
    Vermont
    VT 1

  11. #11
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT
        s.Name
      , s.Value
      , Count(*) AS c
    FROM tblStates AS s
    LEFT JOIN tblVendors AS v ON
      v.State = s.Value
      AND v.Active = Yes
    GROUP BY s.Name, s.Value
    it's very important you put tblStates in the FROM, otherwise, you will NEVER get states that have no vendors.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    longneck, you are still using count(*), that's always going to return at least a 1, even for states that have no vendor!!

    you want count(v.State) instead

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    yeah, i know. i realized that as i was leaving my desk. *smack*

  14. #14
    SitePoint Evangelist NokX's Avatar
    Join Date
    Feb 2003
    Location
    Knoxville, TN
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    Code:
    SELECT
           s.Name
         , s.Value
         , Count(*) AS c
       FROM tblStates AS s
       LEFT JOIN tblVendors AS v ON
         v.State = s.Value
         AND v.Active = Yes
       GROUP BY s.Name, s.Value
    it's very important you put tblStates in the FROM, otherwise, you will NEVER get states that have no vendors.
    i'm still just getting states with vendors in them...

    SELECT s.Name, s.Value, COUNT(v.State) AS c
    FROM tblStates AS s LEFT JOIN tblVendors AS v ON v.State = s.Value
    WHERE v.Active = Yes
    GROUP BY s.Name, s.Value
    ORDER BY s.Name

    returns...

    Name Value c
    California CA 3
    Connecticut CT 2
    Georgia GA 1
    Illinois IL 5
    Indiana IN 1
    Michigan MI 1
    Montana MT 1
    New Jersey NJ 1
    North Carolina NC 1
    Ohio OH 1
    Tennessee TN 35
    Vermont VT 1

    however - if i take out the WHERE clause...

    SELECT s.Name, s.Value, COUNT(v.State) AS c
    FROM tblStates AS s LEFT JOIN tblVendors AS v ON v.State = s.Value
    GROUP BY s.Name, s.Value
    ORDER BY s.Name

    it works...but i really need to weed out the inactive vendors...

    Name Value c
    Alabama AL 0
    Alaska AK 0
    American Samoa AS 0
    Arizona AZ 0
    Arkansas AR 0
    California CA 3
    Colorado CO 0
    Connecticut CT 2
    Delaware DE 0
    District of Columbia DC 0
    Federated States of Micronesia FM 0
    Florida FL 0
    Georgia GA 1
    Guam GU 0
    Hawaii HI 0
    Idaho ID 0
    Illinois IL 5
    Indiana IN 1
    Iowa IA 0
    Kansas KS 0
    Kentucky KY 0
    Louisiana LA 0
    Maine ME 0
    Marshall Islands MH 0
    Maryland MD 0
    Massachusetts MA 0
    Michigan MI 1
    Minnesota MN 0
    Mississippi MS 0
    Missouri MO 0
    Montana MT 1
    Nebraska NE 0
    Nevada NV 0
    New Hampshire NH 0
    New Jersey NJ 1
    New Mexico NM 0
    New York NY 0
    North Carolina NC 1
    North Dakota ND 0
    Northern Mariana Islands MP 0
    Ohio OH 1
    Oklahoma OK 0
    Oregon OR 0
    Palau PW 0
    Pennsylvania PA 0
    Puerto Rico PR 0
    Rhode Island RI 0
    South Carolina SC 0
    South Dakota SD 0
    Tennessee TN 37
    Texas TX 0
    Utah UT 0
    Vermont VT 1
    Virgin Islands VI 0
    Virginia VA 0
    Washington WA 0
    West Virginia WV 0
    Wisconsin WI 0
    Wyoming WY 0

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    oh, i see it -- i wasn't looking beyond the count(*) problem

    move the condition v.Active = Yes out of the WHERE clause and into the ON clause of the LEFT OUTER JOIN

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Evangelist NokX's Avatar
    Join Date
    Feb 2003
    Location
    Knoxville, TN
    Posts
    523
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you da man - you all da men. i appreciate it. it was driving me nuts.


Bookmarks

Posting Permissions

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