Operand should contain 1 column(s)

Question

So I am trying to write a SQL statement as follows...

I need to Find a list of artist names, the number of CDs they have produced, and the average price for their CDs. Only returning results for artists with more than one CD.

Problem is every time i think ive got it I get an error reading "Operand should contain 1 column(s)", I feel as if I am being incredibly dense right now but if someone could help I would appreciate it so much.

Here is the code I have tried last..

SELECT Artist.artID, CD.cdPrice, Count(*)
FROM CD
INNER JOIN Artist
ON Artist.artID=(SELECT CD.artID, AVG(CD.cdPrice), COUNT(*)
                 as Count FROM CD GROUP BY CD.artID HAVING Count > 1)

G B · Accepted Answer · 2015-03-15T22:26:01.527

0

Your join has three columns on the right hand side so you could use something like

SELECT Artist.artID, CD.cdPrice, Count(*)
FROM CD
INNER JOIN Artist
ON Artist.artID=(SELECT CD.artID
                 FROM CD 
                 GROUP BY CD.artID 
                 HAVING Count(*) > 1)
Group by Artist.artID, CD.cdPrice

ALTERNATIVE

SELECT Artist.artID, CD.cdPrice, Count(*)
FROM CD
INNER JOIN Artist
ON Artist.artID=CD.artID
Group by Artist.artID, CD.cdPrice

ALTERNATIVE 2

SELECT Artist.artID, AVG(CD.cdPrice), Count(*)
FROM CD
INNER JOIN Artist
ON Artist.artID=CD.artID
Group by Artist.artID
Having count(*) >1

edited Mar 15 '15 at 22:26

answered Mar 15 '15 at 22:08

G B

1,412
10
12

That still doesnt work though? that returns an error "#1242 - Subquery returns more than 1 row " – TyKitsune Mar 15 '15 at 22:10
I was only pointing out the source of the error, see the alternative and let me know why that doesn't work. – G B Mar 15 '15 at 22:14
Okay well with the Alternative one, that removes the ability i believe to return the Average price and the number of CDs the artists have produced. As whenever i try and add these once more the original error reappears, Some artists have produced more than 1 CD and I need those CDs counted so it just reads the name of the artist and the number of CDs, then I just need to return only those Artists who have made more than 1 CD only.. I found using the alternative method, It just returned back to the original error when i tried to add these parameters in. – TyKitsune Mar 15 '15 at 22:21
YES! that works perfectly thank you so much! I didn't realize that was how you did that thank you so much! – TyKitsune Mar 15 '15 at 22:32

score 0 · Answer 2 · answered Mar 15 '15 at 22:08

0

you are saying WHERE artID= three columns That is the issue

answered Mar 15 '15 at 22:08

Leandro Papasidero

3,728
1
18
33

Operand should contain 1 column(s)

2 Answers2