Select Interweaving - 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 2008 Forums

Transact-SQL (2008)

Select Interweaving

Author

Topic

Sood
Starting Member

2 Posts

Posted - 2012-10-24 : 04:37:42

Hey there,

I'm novice in MySQL Programming, just began to learn it two weeks ago, and I met a problem in an exercice my trainer gave.

I had to create a Database classifying datas concerning the set up of musical meets. Here is a part of it in touch with my following question:


AirDeJava
    dbo.GET_MEMBERS
        ID_MEMBER(PK, int, not NULL)
        ID_BAND(PK, int, not NULL)
    dbo.GROUPE
        ID_BAND(PK, int, not NULL)
        BANDNAME(varchar(30), NULL)

The aim of the exercise is to show the name of the bands and the number of members of the bands having more members than the average.

Don't worry, I don't expect you to do the exercise in place of me (Don't take MY work

!). That's what I made first:


SELECT COUNT(DISTINCT ID_MEMBER) As NUMBER_MEMBERS
FROM GET_MEMBERS
GROUP BY ID_BAND

I tried this next:


SELECT AVG(NUMBER_MEMBERS) As MOYENNE_MEMBRE
FROM (
SELECT COUNT(DISTINCT ID_MEMBER) As NUMBER_MEMBERS
FROM GET_MEMBERS GROUP BY ID_BAND)

Obviously... That don't work.
A helping hand could come like providance, I thank you beforehand.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-10-24 : 05:00:31

Is it possible for someone to be a member of two bands?
If not then the PK of GET_MEMBERS should just be ID_MEMBER.

The distinct is not needed in your first query
SELECT COUNT(*) As NUMBER_MEMBERS

The last query just need to name the derived table

SELECT AVG(NUMBER_MEMBERS) As MOYENNE_MEMBRE
FROM (
SELECT COUNT(DISTINCT ID_MEMBER) As NUMBER_MEMBERS
FROM GET_MEMBERS GROUP BY ID_BAND) as a

Just noticed this is MySQL so this might not be right.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Sood
Starting Member

2 Posts

Posted - 2012-10-24 : 05:22:35

That works like a charm, thanks for your quickly answer!
I'll post the complete answer of the exercise when I finish it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources