CODEWITHSUNDEEP
mysqlsql
MoteCL
MoteCL

Reputation: 228

Query SELECT DISTINCT count()

Hello there I have the following doubt I want to count how many times in a month I enter data. My database is:

I have the following query.

SELECT DISTINCT (date)
    FROM employee 
    WHERE date 
    IN (SELECT date 
    FROM employee 
GROUP BY date 
HAVING count( date ) >0) 
ORDER BY date DESC;

This query gives me: Date: 10/2017 8/2017 9/2017

But I want you to give me something like that.

I hope I have explained my regards.

Upvotes: 0

Views: 62

Answers (3)

Grow Up
Grow Up

Reputation: 21

No need to use the "HAVING" just use like below:

SELECT `date`, count(*) 
FROM `employee`
GROUP BY `date`
ORDER BY `date` DESC;

Upvotes: 0

Shuddh
Shuddh

Reputation: 1970

The query you wrote is a bit complicated. Distinct and group by are doing the same thing for you here. When you do a group by count will automatically give you the count of grouped rows. Also you will have unique dates as well. Try this.

SELECT count(date), date
    FROM employee
GROUP BY date 
HAVING count( date ) >0
ORDER BY date DESC;

Upvotes: 1

Uueerdo
Uueerdo

Reputation: 15961

You're overcomplicating it; no subquery, or DISTINCT, needed.

SELECT `date`, count(*) 
FROM `employee`
GROUP BY `date`
HAVING count(*) > 0
ORDER BY `date` DESC;

I am a little confused as to what reason you would have for the HAVING count() > 0 though; the only way something could have a zero count would mean it wasn't in the table (and therefore wouldn't show up anyway).

Other observations:

  • DISTINCT is not a function; enclosing the date in parenthesis in the SELECT clause has absolutely no effect. (Also, DISTINCT is almost never appropriate for a GROUPing query.)
  • COUNT(somefield) is the same as COUNT(1), COUNT(*). If you want the count of unique values you can do COUNT(DISTINCT somefield); but it wouldn't make sense to COUNT(DISTINCT groupingfield) as that would always result in 1.

Upvotes: 3

Related Questions