Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SELECT DISTINCT HAVING Count unique conditions

Tags:

sql

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!

like image 549
vfxdev Avatar asked Nov 06 '12 19:11

vfxdev


People also ask

How do I use count and distinct together in SQL?

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.

Can you use distinct in the HAVING clause?

The condition in the HAVING clause cannot include a DISTINCT or UNIQUE aggregate expression.

Is it count distinct or distinct count?

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.

Does count distinct count nulls SQL?

DISTINCT returns a row for a single NULL as a distinct value, but COUNT DISTINCT does not count NULL.


2 Answers

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

like image 154
Michael Berkowski Avatar answered Oct 15 '22 02:10

Michael Berkowski


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
  )
like image 32
fthiella Avatar answered Oct 15 '22 02:10

fthiella