CODEWITHSUNDEEP
mysql
user1753403
user1753403

Reputation: 33

How to make a query to return the number of rows grouped by day?

I am using this expression:

SELECT DATE(calldate) as Data,COUNT(clid) as Registros,dst as Tronco
FROM cdr WHERE dst=55008133070000 
AND calldate BETWEEN '2012-10-01' AND '2012-10-15'
GROUP BY DATE(calldate), clid HAVING COUNT(clid) > 1 ORDER BY Data DESC

Date---------Total------ Trunk
2012-10-11  3   55008133070000
2012-10-11  2   55008133070000
2012-10-11  3   55008133070000
2012-10-10  2   55008133070000
2012-10-10  2   55008133070000
2012-10-10  2   55008133070000
2012-10-09  3   55008133070000
2012-10-09  2   55008133070000
2012-10-09  2   55008133070000
2012-10-09  3   55008133070000
2012-10-09  5   55008133070000
2012-10-09  5   55008133070000

However, needs to be shown the amount of times that day repeated.

Date---------Total------ Trunk
2012-10-11  3   55008133070000
2012-10-10  3   55008133070000
2012-10-09  6   55008133070000

Any suggestions?

Upvotes: 2

Views: 113

Answers (3)

dani herrera
dani herrera

Reputation: 51655

EDITED due OP comment:

You should group by dst and not by clid, also count for distinct destination number, see GROUP BY clause and COUNT:

SELECT DATE(calldate) as Data,COUNT( distinct clid) as Registros, dst as Tronco
FROM cdr 
WHERE dst=55008133070000 
      AND calldate BETWEEN '2012-10-01' AND '2012-10-15'
GROUP BY DATE(calldate),  dst 
HAVING COUNT(distinct clid) > 1 ORDER BY Data DESC

Upvotes: 0

Vinit Prajapati
Vinit Prajapati

Reputation: 1613

Instead of Id, do using Calldate.

SELECT DATE(calldate) as Data,COUNT(calldate) as Registros,dst as Tronco
FROM cdr WHERE dst=55008133070000 
AND calldate BETWEEN '2012-10-01' AND '2012-10-15'
GROUP BY DATE(calldate), dst HAVING COUNT(calldate) > 1 ORDER BY Data DESC

Upvotes: 0

newfurniturey
newfurniturey

Reputation: 38416

In your query, you're using GROUP BY DATE(calldate), clid which will give you the COUNT() for each clid on each day.

Remove the clid from the GROUP BY clause to get a count for each date.

Your final query would look similar to:

SELECT
    DATE(calldate) as Data, COUNT(*) as Registros, dst as Tronco
FROM
    cdr
WHERE
    dst=55008133070000 
    AND calldate BETWEEN '2012-10-01' AND '2012-10-15'
GROUP BY
    DATE(calldate)
HAVING
    COUNT(clid) > 1
ORDER BY
    Data DESC

In this query, however, pulling dst may not make sense and could be removed.

Upvotes: 2

Related Questions