sql - 有没有办法计算两次相同的日期列？

Question

select CONVERT(VARCHAR(10),accounting.dates,103) as date_of_acc, numbers,
count(accounting.dates) as total from #a
left join accounting on #a.code = accounting.code
where numbers in (5,74)
group by accounting.dates,numbers
order by date_of_acc,numbers;




 date_of_acc    numbers  total
 01/07/2019        5      43
 01/07/2019        5      53
 01/10/2019        5      72
 01/10/2019        5      40
 01/10/2019        5      44
 01/10/2019        5      71
 01/10/2019        5      76
 01/10/2019        5      77

我需要再次计算“date_of_acc”列，因为他没有特别在一个日期中计算我的全部，我需要的是这个日期“01/07/2019”成为一个值，并且总和需要合而为一。这也适用于其他日期值。

Answer 1

您可以尝试以下 - 在分组依据中添加 CONVERT(VARCHAR(10),accounting.dates,103)

select CONVERT(VARCHAR(10),accounting.dates,103) as date_of_acc, numbers,
count(accounting.dates) as total from #a
left join accounting on #a.code = accounting.code
where numbers in (5,74)
group by CONVERT(VARCHAR(10),accounting.dates,103),numbers
order by date_of_acc,numbers