AbsoluteBeginner AbsoluteBeginner - 8 days ago 5
MySQL Question

Display the orders in which more than one article is ordered

I tried to display the entries in table where the order has more than one article: but it´s not working the way I tried it. Can somebody show me what´s wrong?!

Here´s what I tried:

SELECT *
FROM TableX
WHERE (SELECT COUNT(Ordernumber) FROM TableX AS a WHERE a>1);

Answer

One option is to use a subquery to identify the order numbers having more than one article, then join this subquery to your original table to obtain the full records for these matching orders.

SELECT t1.*
FROM TableX t1
INNER JOIN
(
    SELECT Ordernumber
    FROM TableX
    GROUP BY Ordernumber
    HAVING COUNT(*) > 1
) t2
    ON t1.Ordernumber = t2.Ordernumber

This query assumes that all articles within a given order are unique. If duplicate articles could occur, and you would not count duplicates, then you can use the following HAVING clause instead:

HAVING COUNT(DISTINCT article) > 1

Another option:

SELECT *
FROM TableX
WHERE Ordernumber IN
(
    SELECT Ordernumber
    FROM TableX
    GROUP BY Ordernumber
    HAVING COUNT(*) > 1
)
Comments