How to Count Days in SQL

Question

I'm facing difficulty in generating top below table, using following data, please help!

text | date
-----------------
aa   | 25-08-2017
aa   | 26-08-2017
aa   | 26-08-2017
aa   | 26-08-2017
aa   | 27-08-2017
ab   | 25-08-2017
ab   | 25-08-2017
bb   | 25-08-2017
bb   | 26-08-2017
bb   | 26-08-2017
bb   | 26-08-2017

I need report like,

text | days
-----------
aa   | 3
bb   | 2
ab   | 1

Is it just count of the rows or you want difference of the dates? — Gurwinder Singh, Oct 03 '17 at 11:30
SQL has a function called `count()`. If you don't know how to use it, I've heard good things about the book, Teach Yourself SQL in 10 Minutes. — Dan Bracuk, Oct 03 '17 at 11:30
I think the challenge he is facing in printing the last line having count only 1 — Ankit Bajpai, Oct 03 '17 at 11:32
@Ankit What's your point? There is only one `ab` row in his sample table. — Tomalak, Oct 03 '17 at 11:35
@venkatesh `select text,count(date) As days from table group by text` — ADNAN ZAMAN, Oct 03 '17 at 12:01

Radim Bača · Answer 1 · 2017-10-03T11:33:59.660

-1

SELECT CASE WHEN t.ct = 1 THEN concat(t.text,  ' count ', t.ct, ' day') 
            ELSE concat(t.text,  ' days count ', t.ct) 
       END
FROM (
   SELECT text, count(date) AS ct
   FROM your_table 
   GROUP BY text
) t

edited Oct 03 '17 at 11:33

answered Oct 03 '17 at 11:30

Radim Bača

10,646
1
19
33

Maybe it should be `count(distinct date)` – sagi Oct 03 '17 at 11:31
Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its long-term value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you've made. – Toby Speight Oct 03 '17 at 12:33

score -1 · Answer 2 · answered Oct 03 '17 at 11:30

-1

I recommend you using the fonction timediff() https://www.w3resource.com/mysql/date-and-time-functions/mysql-timediff-function.php

answered Oct 03 '17 at 11:30

1) The OP is not asking about time difference. 2) You can't recommend specific functions unless you know the SQL engine the OP uses. 3) If you link, link to credible sources, preferably the original documentation. `w3resource` is not a credible source. – Tomalak Oct 03 '17 at 11:33

How to Count Days in SQL

2 Answers2