GROUP BY - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

GROUP BY

Author

Topic

soulchyld21
Starting Member

28 Posts

Posted - 2012-05-02 : 15:44:20

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!

soulchyld21
Starting Member

28 Posts

Posted - 2012-05-02 : 15:47:45

heres the query that is giving me unexpected results

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,

shilpash
Posting Yak Master

103 Posts

Posted - 2012-05-02 : 17:14:18

is it contactname or countryname

soulchyld21
Starting Member

28 Posts

Posted - 2012-05-02 : 17:19:08

doestnt really matter, point is it still returns an unexpected result, but yes it is countryname

shilpash
Posting Yak Master

103 Posts

Posted - 2012-05-02 : 17:32:35

can u post a screenshot of ur result

shilpash
Posting Yak Master

103 Posts

Posted - 2012-05-02 : 22:01:51

quote:
Originally posted by soulchyld21

doestnt really matter, point is it still returns an unexpected result, but yes it is countryname

I dont know how you got 91 with country
you get 91 with contact name only
but you get 69 from country.

City (No column name) ContactName
Madrid 1 Alejandra Camino
Leipzig 1 Alexander Feuer
México D.F. 1 Ana Trujillo
Sao Paulo 1 Anabela Domingues
Campinas 1 André Fonseca
London 1 Ann Devon
Toulouse 1 Annette Roulet
México D.F. 1 Antonio Moreno
Sao Paulo 1 Aria Cruz
Lander 1 Art Braunschweiger
Rio de Janeiro 1 Bernardo Batista
Nantes 1 Carine Schmitt
Barquisimeto 1 Carlos González
San Cristóbal 1 Carlos Hernández
Bruxelles 1 Catherine Dewey
Luleå 1 Christina Berglund
Versailles 1 Daniel Tonini
Madrid 1 Diego Roel
Paris 1 Dominique Perrier
Barcelona 1 Eduardo Saavedra
London 1 Elizabeth Brown
Tsawassen 1 Elizabeth Lincoln
I. de Margarita 1 Felipe Izquierdo
Portland 1 Fran Wilson
México D.F. 1 Francisco Chang
Strasbourg 1 Frédérique Citeaux
Salzburg 1 Georg Pipps
Bergamo 1 Giovanni Rovelli
México D.F. 1 Guillermo Fernández
Mannheim 1 Hanna Moos
London 1 Hari Kumar
Cowes 1 Helen Bennett
Kirkland 1 Helvetius Nagy
Köln 1 Henriette Pfalzheim
Cunewalde 1 Horst Kloss
Eugene 1 Howard Snyder
Lisboa 1 Isabel de Castro
San Francisco 1 Jaime Yorres
Rio de Janeiro 1 Janete Limeira
Nantes 1 Janine Labrune
Montréal 1 Jean Fresnière
Walla Walla 1 John Steel
Stavern 1 Jonas Bergulfsen
Boise 1 Jose Pavarotti
Sevilla 1 José Pedro Freyre
Kobenhavn 1 Jytte Petersen
Münster 1 Karin Josephs
Seattle 1 Karl Jablonski
Marseille 1 Laurence Lebihan
Lisboa 1 Lino Rodriguez
Butte 1 Liu Wong
Portland 1 Liz Nixon
Sao Paulo 1 Lúcia Carvalho
Caracas 1 Manuel Pereira
Berlin 1 Maria Anders
Bräcke 1 Maria Larsson
Paris 1 Marie Bertrand
Rio de Janeiro 1 Mario Pontes
Madrid 1 Martín Sommer
Lille 1 Martine Rancé
Lyon 1 Mary Saveley
Helsinki 1 Matti Karttunen
Reggio Emilia 1 Maurizio Moroni
Genève 1 Michael Holz
México D.F. 1 Miguel Angel Paolino
Århus 1 Palle Ibsen
Torino 1 Paolo Accorti
Charleroi 1 Pascale Cartrain
Cork 1 Patricia McKenna
Buenos Aires 1 Patricio Simpson
Reims 1 Paul Henriot
Resende 1 Paula Parente
Albuquerque 1 Paula Wilson
Sao Paulo 1 Pedro Afonso
München 1 Peter Franken
Brandenburg 1 Philip Cramer
Oulu 1 Pirkko Koskitalo
Frankfurt a.M. 1 Renate Messner
Anchorage 1 Rene Phillips
Stuttgart 1 Rita Müller
Graz 1 Roland Mendel
Buenos Aires 1 Sergio Gutiérrez
London 1 Simon Crowther
Aachen 1 Sven Ottlieb
London 1 Thomas Hardy
London 1 Victoria Ashworth
Bern 1 Yang Wang
Elgin 1 Yoshi Latimer
Vancouver 1 Yoshi Tannamuri
Buenos Aires 1 Yvonne Moncada
Warszawa 1 Zbyszek Piestrzeniewicz

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-03 : 07:17:07

quote:
Originally posted by shilpash

is it contactname or countryname


Can you provide the sample data ..

In case of Group by clause what exactly happens this group clause group the data on the basis of columns you have specified...

If you send the Sample data we can provide you the answer for it.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-05-03 : 07:51:01

This gives the 69 rows:

SELECT Country, City, count(*)
FROM Customers
GROUP BY Country,City
order by Country,City

No, you're never too old to Yak'n'Roll if you're too young to die.

soulchyld21
Starting Member

28 Posts

Posted - 2012-05-03 : 09:13:26

Hi, thanks I seem to have found a way around it all, It was my misunderstanding of the group by clause,
when using it we have to add every column not in an aggregate function to the group by clause, if one wanted to have the data from other columns but didnt want the result grouped on those grounds is there a way to work around it? say if one only wanted to group by the ID (PK) and needed to sum() say library visits but also needed the phone number?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-05-03 : 09:29:27

you can use max(Phone) or min(Phone) if you like

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources