Thread: Grouping

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    1
    Rep Power
    0

    Grouping


    Hi,

    So I have query that looks like this
    SELECT C.CITYNAME, W.CAPACITY
    FROM CITY C
    INNER JOIN WAREHOUSE W
    ON C.CITYCODE = W.CITYCODE
    WHERE W.CAPACITY >=10000
    Which presents this: puu.sh/2spff

    How can I make it add up all the cities population respectively to arrive at each city being presented once?

    For example, it would show Melbourne as 50,000 (20,000+30,000)
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,209
    Rep Power
    4279
    Originally Posted by Acerman1254
    How can I make it add up all the cities population respectively to arrive at each city being presented once?
    by using the SUM aggregate function along with GROUP BY
    Code:
    SELECT c.cityname
         , SUM(w.capacity) AS total_capacity
      FROM city c
    INNER 
      JOIN warehouse w
        ON w.citycode = c.citycode
       AND w.capacity >= 10000 
    GROUP
        BY c.cityname
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo