Post reply

Grouping

  • May 2, 2012 at 2:00 pm

    #392173

    Hi I am learning sql and am using the famous northwind database,

    I am trying to learn to use aggregate functions such as count()

    I am able to get the required result using this query which simply gives me a city and a count of occurences in the db, I get 69 records 🙂

    SELECT COUNT(city) as NoCity, city

    FROM Customers

    GROUP BY City

    I need a query that further gives me the country in which these cities are found, also in the db! however when I use the query below I get different results to what I require, The result from this query treats every occurence of the city as one and I get 91 records from the table, I would like to write a query that gives me the 69 from above with the countries and the count of the city occurence!

    Please point me in the right direction!

    SELECT COUNT(city)As NoOfCity , city, ContactName

    FROM Customers

    GROUP BY city, ContactName

    it is counting but treating each occurence with a different ContactName as new city and thus giving me 91 records,

  • May 2, 2012 at 2:44 pm

    #1482699

    Soul, let me see if I understand what you're looking for. You're basically looking for something that looks like:

    82 | Madrid | USA, Spain, Brazil

    Or something that looks like:

    82 | Madrid | USA

    82 | Madrid | Spain

    82 | Madrid | Brazil

    If you can take a look at the first link in my signature that will help you understand what will give us the best chance of helping you in the future, in this case the results section in particular. I personally don't have a copy of northwind handy but the SQL isn't that extravagant in this case.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • May 2, 2012 at 2:56 pm

    #1482706

    In your description you said you wanted Country but in your code you used ContactName. Could it be as simple as using the correct column?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • May 2, 2012 at 3:22 pm

    #1482724

    Evil Kraig, yes that is simmilar to what Im looking for!!

    Unfortunately its not as simple as the wrong data as suggested by the 2nd poster!

  • May 2, 2012 at 3:24 pm

    #1482725

    soulchyld21 (5/2/2012)

    Evil Kraig, yes that is simmilar to what Im looking for!!

    Unfortunately its not as simple as the wrong data as suggested by the 2nd poster!

    Which version of Kraig's is the one you are looking for?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • May 2, 2012 at 3:29 pm

    #1482727

    Taking a guess that is the first version you are looking for.

    Something like should get you started.

    ;with CityData(City, Country) as

    (

    select 'Madrid', 'USA' union all

    select 'Madrid', 'Spain' union all

    select 'Madrid', 'Brazil'

    )

    select City,

    STUFF((select ', ' + Country

    From CityData c2

    where c1.City = c2.City

    order by c2.Country

    FOR XML PATH('')), 1, 1, ' ')

    from CityData c1

    group by City

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • May 2, 2012 at 7:57 pm

    #1482789

    soulchyld21 (5/2/2012)

    Hi I am learning sql and am using the famous northwind database,

    I am trying to learn to use aggregate functions such as count()

    I am able to get the required result using this query which simply gives me a city and a count of occurences in the db, I get 69 records 🙂

    SELECT COUNT(city) as NoCity, city

    FROM Customers

    GROUP BY City

    I need a query that further gives me the country in which these cities are found, also in the db! however when I use the query below I get different results to what I require, The result from this query treats every occurence of the city as one and I get 91 records from the table, I would like to write a query that gives me the 69 from above with the countries and the count of the city occurence!

    Please point me in the right direction!

    SELECT COUNT(city)As NoOfCity , city, ContactName

    FROM Customers

    GROUP BY city, ContactName

    it is counting but treating each occurence with a different ContactName as new city and thus giving me 91 records,

    That's because you're grouping by ContactName, as well, an you have 91 ContactNames How do you want to display multiple contact names on a single row for each city?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 3, 2012 at 6:57 am

    #1482978

    Thanks guys, It turns out my db design was the problem so it overly complicated the querying process! What I needed was to select some aggregated values, and some contact details from the same table, aggregate functions cant be used without GROUP BY clause and using the group by clause in my query gave me some unexpected results, quite a long winded way of doing it but I created temp tables to hold the data and ran my query against temp tables, got very realistic results from that so hopefully that will work while I work on my DB design flaws, I now see the importance of normalisation!:-)

  • May 3, 2012 at 7:05 am

    #1482987

    @ JEff, When grouping we have to add every column that is not part of an aggregate function to the group by clause, this then alters the results accordingly, (learnt that the hard way lol) is there however a way to have multiple columns not in the group by clause but in the query, say one selected 5 columns, only one had an aggregate function say SUM() and one simply wanted the result grouped by ID?

  • May 3, 2012 at 7:22 am

    #1483004

    There is indeed. But I'd like to see what you think you'd like the output to look like.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply