如何使查询返回按天分组的行数？

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?

3 个解决方案

#1

EDITED due OP comment:

编辑到期OP评论:

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

您应该按dst分组而不是clid,也要计入不同的目标号码,请参阅GROUP BY子句和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

#2

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

在您的查询中,您正在使用GROUP BY DATE(calldate),clid,它将为您提供每天每个clid的COUNT()。

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

从GROUP BY子句中删除clid以获取每个日期的计数。

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.

但是,在此查询中,拉dst可能没有意义,可以删除。

#3

Instead of Id, do using Calldate.

而不是Id,请使用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

#1