Somewhat stuck with just 1 last MYSQL command

[Combat squirrel]

Hey all, from a table for example:

'cd001', 1
'cd002', 1
'cd003', 1
'cd004', 1
'cd005', 1
'cd006', 1
'cd007', 1
'cd008', 1
'cd009', 1
'cd010', 1
'cd011', 2
'cd012', 2
'cd013', 2
'cd014', 2
'cd015', 2
'cd016', 2
'cd017', 2
'cd018', 2
'cd019', 2
'cd020', 2
'cd021', 2
'cd024', 3

Im trying to list the cd_id (cd020 for example) and the number of bands involved (1, 2 or 3 for example), so id end up with the cd number and band 1, shown 14 times or something like that, how would I do that ? any hints? I know I need a group by and having clause in the command line, but for the last 3 hours it evades me!, any pointers would be great


(PS:Usually the PC 'do your own work' brigade jumps in here, its not marked work or anything like that {as term has finished!} its a work shop paper that we can do to brush up on mysql stuff and im doing that! but this one last exercise is evading me)

Iv done this:

SELECT cd_id, COUNT(*) FROM releases GROUP BY band_id;

Results in:

'cd001', 30
'cd011', 33
'cd024', 87
'cd037', 3
'cd045', 6
'cd051', 42
'cd053', 18

Which I guess is counting the cd_id and then saying how many times it appears of each band_id, but I need it to list the cd_id and number of bands involved, any clues ?

 

[Combat squirrel]

hehe, ahh ok, well er lets try:

For each cd on which the bands have collaborated, list the cd_id and the number of bands involved. Collabertion means in that the relation 'releases' (name of the table), there are two or more band_ids associated with one cd_id value, the only hint I have is I need a GROUP BY and HAVING clause.

Tablename : releases

cd_id | band_id

Data as in example, bit more of it, upto 9 bands,lol

That help ? :S, sorry it has a brain stinger! lol

 

[Combat squirrel]

Thanks for the feedback guys, I asked my tutor again who made the work shop, and they insist a HAVING clause is needed ? Interesting.

The ENTIRE database consists of tables (4):

1) CREATE TABLE cds (cd_id CHAR (6), rdate DATE,position INT, title VARCHAR (40), weeks INT,PRIMARY KEY (cd_id));

2) CREATE TABLE bands (band_id INT, bandname VARCHAR (40), PRIMARY KEY (band_id));

3) CREATE TABLE band_members (band_id INT (40), name VARCHAR (40), dob DATE, country VARCHAR (40), sex CHAR (1), PRIMARY KEY (band_id));

4) CREATE TABLE releases (cd_id INT, band_id VARCHAR (40) PRIMARY KEY (band_id));


They pointed out:

Where are the NOT NULLs?
In table band_members attribute band_id cannot uniquely identify a row – a band can have many band members.

Also abit further into the database they pointed out after I altered the table:

You appear to be making band_id a foreign key of table bands, this does not make sense. Band_id will only be a foreign key in one of the tables not both. This is possibly why you could not load data? Could have been getting into a tangle.

Made a woopsey there I think, I guess this would effect the OP situation?

Damn this is driving me up the wall, I cant sleep until it works

 

[Combat squirrel]

Can scan the paper if you like beepcake, my tutor is helpful when they get the time, there on holiday most of the time now and get back to me when they get time, so only had 1 set of 'feedback' so far, so its sparse and any knowledge on the forums would be great.

boycee - its a workshop paper (i.e. a mySQL question sheet with tasks and questions on), homework was all in by June 5th

Iv done most of it, its just the last part, ill stick up a coherent explanation of the questions and my answers shortly.

Any pointers is much appreciated so far.

 

[Combat squirrel]

Right hopefully this will make it easier to understand, a shot of the database layout:

The origonal question:

For each cd on which bands have collaborated, list the cd_id and number of bands involved. Collaboration means that in relation releases there are two or more band_ids associated with one cd_id value. Hint: you need a GROUP BY and a HAVING clause.

Makeh anyy more senseh ?

But since, iv found out that the previous question to that:

For those bands that have had a number 1 hit, list the name of the band and the total number of their records that made it to number 1. Hint: You need to use GROUP BY to get groups of band names and you need a COUNT(*) in the SELECT part of the statement

I got wrong, so any clues? lol

 

[Combat squirrel]

Hi simon, many thanks for your feedback, tried those commands no go unfort, tried this for the origonal question:

SELECT cd_id, count(band_id)FROM releases having count(band_id) > 1 group by band_id;

Unfort kicks out a syntax 1064 error, it looks logically corrrect, struggling to see why it still not works ?

EDIT: Doesnt seem to like the group by clause


THIS WORKED:

SELECT cd_id, count(band_id)FROM releases GROUP BY band_id having count(band_id) > 1;

'cd001', 20
'cd011', 22
'cd024', 58
'cd037', 2
'cd045', 4
'cd051', 28
'cd053', 12