CODEWITHSUNDEEP
mysqlsql
jeninja
jeninja

Reputation: 848

SQL Subquery using Union mixing up results

I am trying to find out which dealer has the most blue cars. The table schema is below:

AVAILABLE_AUTO(SERIAL_NO,MODEL,COLOR,DEALER)

I have the following query that returns the following results:

SELECT COUNT(*) AS COUNT, DEALER
FROM global.AVAILABLE_AUTO
WHERE COLOR = "blue" AND DEALER = "DEALER ONE"
UNION
SELECT COUNT(*) AS COUNT, DEALER
FROM global.AVAILABLE_AUTO
WHERE COLOR = "blue" AND DEALER = "DEALER TWO";

|COUNT|DEALER    |
|----------------|
|1    |DEALER ONE|
|2    |DEALER TWO|

I am trying to select the Max of these two results, so I use the following query:

SELECT MAX(COUNT) AS COUNT, DEALER FROM
(
SELECT COUNT(*) AS COUNT, DEALER
FROM global.AVAILABLE_AUTO
WHERE COLOR = "blue" AND DEALER = "DEALER ONE"
UNION
SELECT COUNT(*) AS COUNT, DEALER
FROM global.AVAILABLE_AUTO
WHERE COLOR = "blue" AND DEALER = "DEALER TWO"
) AS T1;

But it produces incorrect results:

|COUNT|DEALER    |
|----------------|
|2    |DEALER ONE|

Dealer in this case should be DEALER TWO

Upvotes: 0

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

Just use a single query with GROUP BY and LIMIT:

SELECT COUNT(*) AS COUNT, DEALER
FROM global.AVAILABLE_AUTO
WHERE COLOR = 'blue' AND DEALER IN ('DEALER ONE', 'DEALER TWO')
GROUP BY DEALER
ORDER BY COUNT(*) DESC
LIMIT 1;

Your final query is malformed because DEALER is not in the GROUP BY but is in the SELECT.

Upvotes: 1

Related Questions