按日期输出组sql查询(group sql query output by date)

按日期输出组sql查询(group sql query output by date)

我有两张桌子用于生成报告。

我如何在报告中获得x轴以显示每月或两个月的结果？

我已经尝试了这个，但是通过输入日期添加组，但它分解了所有的情绪和计数。

SELECT ConceptName,COUNT(distinct blogId) As Tot FROM conceptTable ct JOIN blog on content LIKE '%'+ct.conceptName+'%' GROUP BY conceptName

所以目前它看起来像这样：

相反，我希望它每隔几个月列出轴上的输入日期，以及那个月出现的概念运动的总时间。

I've got a two tables I'm using to generate a report.

How would I get x axis on the report to show the results by say every month or two months?

I've tried this but by adding group by inputdate as well but it splits up all the emotions and counts.

SELECT ConceptName,COUNT(distinct blogId) As Tot FROM conceptTable ct JOIN blog on content LIKE '%'+ct.conceptName+'%' GROUP BY conceptName

so at the moment it looks like this:

instead I would like it to list inputdate on axis for every couple of months and the total times that conceptemotion showed up for that month.

最满意答案

你可能会喜欢这样的事情

select ConceptName,cast(datepart(year, inputdate) as varchar) + '/' + cast(datepart(month, inputdate) as varchar) as RptMonth, COUNT(*) as Tot from conceptTable ct join blog on content like '%'+ct.conceptName+'%' group by conceptName, cast(datepart(year, inputdate) as varchar) + '/' + cast(datepart(month, inputdate) as varchar)

但是你可能还需要一个左边的连接到你的情绪查找表，所以如果那个月都不存在，它仍会表现出情绪。

You're probably after something like

select ConceptName,cast(datepart(year, inputdate) as varchar) + '/' + cast(datepart(month, inputdate) as varchar) as RptMonth, COUNT(*) as Tot from conceptTable ct join blog on content like '%'+ct.conceptName+'%' group by conceptName, cast(datepart(year, inputdate) as varchar) + '/' + cast(datepart(month, inputdate) as varchar)

But you probably also need a left join to your emotions lookup table in there, so it will still show emotions if none exist for that month.