CODEWITHSUNDEEP
sqlselect
aiden87
aiden87

Reputation: 969

Single-row subquery returns more than one row - SQL Query

I am writing a SQL Query that returns name of the product that is used the most.

When I run it, I get the Single-row subquery returns more than one row error and I do now know how to fix it.

This is my query.

CREATE OR REPLACE VIEW mostUsed AS
SELECT a.article_name, SUM(p.quantity) AS numberOfItems
FROM ARTICLE a, ITEM p
WHERE p.fk_id_article = a.id_article
GROUP BY a.article_name
HAVING SUM(p.quantity) >= (SELECT MAX(p.quantity) FROM ITEM);

SELECT ARTICLE_NAME, numberOfItems
FROM mostUsed
WHERE numberOfItems = (SELECT MAX(numberOfItems) FROM mostUsed);

Any help on fixing it is appreciated. Thank you!

Upvotes: 1

Views: 1073

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

The problem is the outerquery column's can be referred inside subquery.

(SELECT MAX(p.quantity) FROM ITEM)

Since you used p.quantity value is fetched from the outer table ITEM p instead of Item table in subquery and the outer Item table is grouped by a.article_name so it returned more than one row.

so remove alias P from the subquery your problem will be fixed

SELECT a.article_name, SUM(p.quantity) AS numberOfItems
FROM ARTICLE a, ITEM p
WHERE p.fk_id_article = a.id_article
GROUP BY a.article_name
HAVING SUM(p.quantity) >= (SELECT MAX(quantity) FROM ITEM)

Upvotes: 2

Related Questions