CODEWITHSUNDEEP
mysql
Javi
Javi

Reputation: 506

MySql COUNT datediff values in complex function

I have this SQL Query that returns datediff of number of week

SELECT
  ROUND(DATEDIFF((
    SELECT t.date FROM actividad_newsletters t WHERE t.id_newsletter = t1.id_newsletter AND t1.id_desc = 3 AND (t.id_desc = 5 OR t.id_desc = 7) AND t.date > t1.date ORDER BY t.date LIMIT 1),
    MIN(t1.date)
  )/7, 0) as weeks
  FROM actividad_newsletters t1
  INNER JOIN newsletter t2 ON t1.id_newsletter = t2.id 
GROUP BY id_newsletter
HAVING weeks IS NOT NULL

And gets this results:

0
0
0
0
0
1
1
1
1
1
1
2
2
3
3
3
3
3
3
3
3
3
3
3
.
.
.
117
117
118
119
119

I want do a count for same result like:

total week
----- ----
5     0
6     1
2     2
11    3
.     .
.     .
.     .
2     117
1     118
2     119

How should I do it? I tried with count(weeks) but isn't working

Upvotes: 0

Views: 51

Answers (2)

Javi
Javi

Reputation: 506

SOLVED:

SELECT weeks, COUNT(weeks) FROM (
SELECT
  ROUND(DATEDIFF((
    SELECT t.date FROM actividad_newsletters t WHERE t.id_newsletter = t1.id_newsletter AND t1.id_desc = 3 AND (t.id_desc = 5 OR t.id_desc = 7) AND t.date > t1.date ORDER BY t.date LIMIT 1),
    MIN(t1.date)
  )/7, 0) as weeks
  FROM actividad_newsletters t1
  INNER JOIN newsletter t2 ON t1.id_newsletter = t2.id 
GROUP BY id_newsletter
HAVING weeks IS NOT NULL ) AS count GROUP BY weeks

Upvotes: 0

naveen goyal
naveen goyal

Reputation: 4629

Try This 

Select weeks, count(*) from (SELECT
  ROUND(DATEDIFF((
    SELECT t.date FROM actividad_newsletters t WHERE t.id_newsletter = t1.id_newsletter AND t1.id_desc = 3 AND (t.id_desc = 5 OR t.id_desc = 7) AND t.date > t1.date ORDER BY t.date LIMIT 1),
    MIN(t1.date)
  )/7, 0) as weeks
  FROM actividad_newsletters t1
  INNER JOIN newsletter t2 ON t1.id_newsletter = t2.id 
GROUP BY id_newsletter
HAVING weeks IS NOT NULL) as t group by weeks

Upvotes: 1

Related Questions