I've searched for an answer on this but can't find quite how to get this distinct recordset based on a condition. I have a table with the following sample data:
Type Color Location Supplier
---- ----- -------- --------
Apple Green New York ABC
Apple Green New York XYZ
Apple Green Los Angeles ABC
Apple Red Chicago ABC
Apple Red Chicago XYZ
Apple Red Chicago DEF
Banana Yellow Miami ABC
Banana Yellow Miami DEF
Banana Yellow Miami XYZ
Banana Yellow Atlanta ABC
I'd like to create a query that shows the count of unique locations for each distinct Type+Color where the number of unique locations is more than 1, e.g.
Type Color UniqueLocations
---- ----- --------
Apple Green 2
Banana Yellow 2
Note that {Apple, Red, 1} doesn't appear because there is only 1 location for red apples (Chicago). I think I've got this one (but perhaps there is a simpler method). I'm using:
SELECT Type, Color, Count(Location) FROM
(SELECT DISTINCT Type, Color, Location FROM MyTable)
GROUP BY Type, Color HAVING Count(Location)>1;
How can I create another query that lists the Type, Color
, and Location
for each distinct Type,Color
when the count of unique locations for that Type,Color
is greater than 1? The resulting recordset would look like:
Type Color Location
---- ----- --------
Apple Green New York
Apple Green Los Angeles
Banana Yellow Miami
Banana Yellow Atlanta
Note that Apple, Red, Chicago
doesn't appear because there is only 1 location for red apples. Thanks!
Yes, you can use COUNT() and DISTINCT together to display the count of only distinct rows. SELECT COUNT(DISTINCT yourColumnName) AS anyVariableName FROM yourTableName; To understand the above syntax, let us create a table. Display all records from the table using select statement.
The condition in the HAVING clause cannot include a DISTINCT or UNIQUE aggregate expression.
The correct syntax for using COUNT(DISTINCT) is: SELECT COUNT(DISTINCT Column1) FROM Table; The distinct count will be based off the column in parenthesis. The result set should only be one row, an integer/number of the column you're counting distinct values of.
DISTINCT returns a row for a single NULL as a distinct value, but COUNT DISTINCT does not count NULL.
Use a COUNT(DISTINCT Location)
and join against a subquery on Type
and Color
The GROUP BY
and HAVING
clauses as you have attempted to use them will do the job.
/* Be sure to use DISTINCT in the outer query to de-dup */ SELECT DISTINCT MyTable.Type, MyTable.Color, Location FROM MyTable INNER JOIN ( /* Joined subquery returns type,color pairs having COUNT(DISTINCT Location) > 1 */ SELECT Type, Color, /* Don't actually need to select this value - it could just be in the HAVING */ COUNT(DISTINCT Location) AS UniqueLocations FROM MyTable GROUP BY Type, Color /* Note: Some RDBMS won't allow the alias here and you would have to use the expanded form HAVING COUNT(DISTINCT Location) > 1 */ HAVING UniqueLocations > 1 /* JOIN back against the main table on Type, Color */ ) subq ON MyTable.Type = subq.Type AND MyTable.Color = subq.Color
Here is a demonstration
You could write your first query as this:
Select Type, Color, Count(Distinct Location) As UniqueLocations
From Table
Group By Type, Color
Having Count(Distinct Location) > 1
(if you're using MySQL you could use the alias UniqueLocations
in your having
clause, but on many other systems the aliases are not yet available as the having
clause is evaluated before the select
clause, in this case you have to repeat the count on both clauses).
And for the second one, there are many different ways to write that, this could be one:
Select Distinct Type, Color, Location
From Table
Where
Exists (
Select
*
From
Table Table_1
Where
Table_1.Type = Table.Type
and Table_1.Color = Table.Color
Group By
Type, Color
Having
Count(Distinct Location) > 1
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With