CODEWITHSUNDEEP
mysql
Jeff
Jeff

Reputation: 480

Count of one row only once based on another row

I'm having trouble describing what I want to do but the example is the best explanation:

Below is MOSTLY what I want to do but there is another column: Date and I want to count each date only once per store

SELECT key,COUNT(*) AS 'cnt',NAME FROM myTable GROUP BY key

So excluding any data that is not relevant lets say I have:

  KEY  |  DATE
--------------------

  A    |  2019-06-01*
  A    |  2019-06-01
  A    |  2019-06-02*
  A    |  2019-06-03*
  B    |  2019-06-01*
  B    |  2019-06-01
  B    |  2019-06-01
  B    |  2019-06-02*
  C    |  2019-06-01*
  C    |  2019-06-02*

I'd like to get 

A | 3
B | 2
C | 2

Counting the first unique date only for each Key(marked with the *).
Any simple way to pull this off that I'm missing?

Upvotes: 0

Views: 16

Answers (1)

knot22
knot22

Reputation: 2768

SELECT KEY, COUNT(DISTINCT DATE) AS 'cnt' FROM myTable GROUP BY KEY;

Upvotes: 2

Related Questions