如何在组中输出值不在结果中？ [英] How to Out Put Value in group by that is not in the result ?

问题描述

Hello Every One，

i有一个查询，用于提取特定时间段内每小时的销售额

，以了解业务在一天中的哪个小时茁壮成长它在哪个方面挣扎



查询如下：

Hello Every One ,
i have a query that extracts the sales per hour in a particular time period
to know in which hour of the day does the business thrive and in which does it struggle

the query goes like this :

select top(24) Datepart(HOUR,inv.invoiceDate) as Hr , sum(iif(inv.invoicetype=1,inv.totalafterdiscount , -inv.totalafterdiscount)) as Amount
    from Invoices inv
    where (inv.invoicetype = 1 or inv.invoicetype = 3) and inv.store = @storeid and  (inv.invoicedate between @StartDate and @EndDate)
    group by Datepart(HOUR,inv.invoiceDate)
    order by Hr

问题是没有销售的时间没有出现在结果中

考虑到查询的编写方式，这是非常可以理解的



我想要的是使结果中出现没有销售的小时数量值为零



我怎样才能实现这个目标？





感谢每一个人花时间阅读本文并帮助我，

非常感谢。

the problem is that the hours in which no sales happened doesn't appear in result
which is very understandable given the way the query is written

what i want is to make the hours in which no sales took place appear in the result with amount value of zero

how can i achieve that ?


Thanks every one for taking time to read this and for helping me ,
really appreciated.

推荐答案

有多种方法，但您可以使用包含连接创建一个小时表，并将null设置为零，或者将所有两个表和组结果按小时联合。您可以通过代码和组添加小时结果。



请参阅 http://blog.jooq.org/2013/11/19 /如何创建范围从1到10-in-sql / [ ^ ]

There are a number of ways, but you could create a small table of hours with an inclusive join and setting null to zero or union all the two tables and group results by hour. You could just add the hour result via code and group.

See http://blog.jooq.org/2013/11/19/how-to-create-a-range-from-1-to-10-in-sql/[^]

这篇关于如何在组中输出值不在结果中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！