simple query - 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

SQL Server 2000 Forums

Transact-SQL (2000)

simple query

Author

Topic

ippsioppsi
Starting Member

6 Posts

Posted - 2008-03-13 : 13:54:16

Hello everybody!
First of all I apologize if this is not the right forum for my question.

My problem is the following.
I have a table (called musicTable) that has the following fields:
MusicType, Band, Album, Song
The relation between the fields is as you can imagine:
there are many songs for every album, there are more albums for each band, there are many bands for every MusicType.

I need a query to extract for every MusicType/Band the number of albums, and the number of songs.

As an example if I have the following data:
cantautore, nomadi, noi ci saremo, Riverisco
cantautore, nomadi, noi ci saremo, la città
cantautore, nomadi, noi ci saremo, La morale
cantautore, nomadi, Gordon, Sorprese
cantautore, nomadi, Gordon, Gordon
cantautore, vasco, liberi liberi, muoviti
cantautore, vasco, liberi liberi, liberi liberi
cantautore, vasco, liberi liberi, dillo alla luna
cantautore, vasco, bollicine, bollicine
cantautore, vasco, bollicine, una canzone per te
cantautore, vasco, bollicine, Portatemi Dio
cantautore, vasco, bollicine, vita spericolata
cantautore, vasco, bollicine, deviazioni
cantautore, vasco, bollicine, giocala

I would expect a result of

cantautore, nomadi, 2, 5
cantautore, vasco, 2, 9

I can extract with two separates query the two count operations, but I don't know How to put them together in a single query.
Any ideas?
Thank you very much,
Ippsi.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-13 : 14:14:37

Soemthing like this?

DECLARE @Music TABLE(MusicType VARCHAR(50), Band VARCHAR(50), Album VARCHAR(50), Song VARCHAR(50))


INSERT @Music
SELECT 'cantautore', 'nomadi', 'noi ci saremo', 'Riverisco'
UNION ALL SELECT 'cantautore', 'nomadi', 'noi ci saremo', 'la città'
UNION ALL SELECT 'cantautore', 'nomadi', 'noi ci saremo', 'La morale'
UNION ALL SELECT 'cantautore', 'nomadi', 'Gordon', 'Sorprese'
UNION ALL SELECT 'cantautore', 'nomadi', 'Gordon', 'Gordon'
UNION ALL SELECT 'cantautore', 'vasco', 'liberi liberi', 'muoviti'
UNION ALL SELECT 'cantautore', 'vasco', 'liberi liberi', 'liberi liberi'
UNION ALL SELECT 'cantautore', 'vasco', 'liberi liberi', 'dillo alla luna'
UNION ALL SELECT 'cantautore', 'vasco', 'bollicine', 'bollicine'
UNION ALL SELECT 'cantautore', 'vasco', 'bollicine', 'una canzone per te'
UNION ALL SELECT 'cantautore', 'vasco', 'bollicine', 'Portatemi Dio'
UNION ALL SELECT 'cantautore', 'vasco', 'bollicine', 'vita spericolata'
UNION ALL SELECT 'cantautore', 'vasco', 'bollicine', 'deviazioni'
UNION ALL SELECT 'cantautore', 'vasco', 'bollicine', 'giocala'



SELECT 
	MusicType,
	Band,
	COUNT(DISTINCT Album) AS AlbumCount,
	COUNT(Song) AS SongCount
FROM
	@Music
GROUP BY
	MusicType,
	Band

ippsioppsi
Starting Member

6 Posts

Posted - 2008-03-13 : 14:45:25

Hello,
thank you for your help Lamprey.
unfortunately I got an error :
Syntax error (missing operator) in query expression 'COUNT(DISTINCT Album)'

Ippsi

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-13 : 16:21:16

Humm, I'm not sure. It works for me on SQL 2000 and 2005...

If you are not using the exact query posted above, please provode that so we can check it over.

ippsioppsi
Starting Member

6 Posts

Posted - 2008-03-13 : 19:43:49

The query is the same, but is executed on an access database (through a dts package installed on a sql server 2000)
Is it possibly different the sql syntax?
thank you again,
ippsi

ippsioppsi
Starting Member

6 Posts

Posted - 2008-03-13 : 20:21:18

I think that microsoft access doesn't accept count (distinct) commands...
after a look to this site
http://blogs.msdn.com/access/archive/2007/09/19/writing-a-count-distinct-query-in-access.aspx

I found the solution:

SELECT T.MusicType, T.[Band], Count(T.Album) AS CountOfAlbum, Sum(T.CS) AS SumOfCS
FROM (SELECT MusicType, Band, Album, Count(SONG) AS CS
FROM Table1
GROUP BY MusicType, Band, Album) AS T
GROUP BY T.MusicType, T.Band;

Thank you Lamprey, sorry to have forget to tell that the database was an access one. And so the topic is OT, apologize for that.
ippsi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources