按日期和总和分组-日期格式和顺序
按日期和总和分组-日期格式和顺序
提问于 2015-06-14 13:21:11
我在试着得到一天销售量的总和。我需要在日期之前把结果整理好。下面的查询给出了我需要的确切结果，除了日期没有格式化为我所需要的。
SELECT CAST(Datetime AS DATE) AS 'date', SUM(quantity) as total_quantity
FROM Invoice_Itemized ii
INNER JOIN Invoice_Totals it ON it.Invoice_Number = ii.Invoice_Number
WHERE ii.ItemNum = '4011'
AND it.datetime > '05/15/2015'
GROUP BY CAST(Datetime AS DATE)
SELECT DATENAME(MM, datetime) + ' ' + CAST(DAY(datetime) AS VARCHAR(2)) AS [DD Month], SUM(quantity) as total_quantity
FROM Invoice_Itemized ii
INNER JOIN Invoice_Totals it ON it.Invoice_Number = ii.Invoice_Number
WHERE ii.ItemNum = '4011'
AND it.datetime > '05/15/2015'
GROUP BY DATENAME(MM, datetime) + ' ' + CAST(DAY(datetime) AS VARCHAR(2))顶部查询的结果：
- 2015-05-15 91.43
- 2015-05-16 84.77
底部查询的结果：
- 128.34年月一日
- 6月10日85.06
顶部的查询按我需要的顺序给出了我需要的信息。下面的查询给出了我需要的日期格式，但顺序不对。
回答 1
Stack Overflow用户
回答已采纳
发布于 2015-06-14 13:27:42
如果确实需要应用不同的格式，可以使用派生表：
select
DATENAME(MM, [date]) + ' ' + CAST(DAY([date]) AS VARCHAR(2)) AS [DD Month],
total_quantity
from
(
SELECT CAST([Datetime] AS DATE) AS [date], SUM(quantity) as total_quantity
FROM Invoice_Itemized ii
INNER JOIN Invoice_Totals it ON it.Invoice_Number = ii.Invoice_Number
WHERE ii.ItemNum = '4011'
AND it.[datetime] > '05/15/2015'
GROUP BY CAST(Datetime AS DATE)
) as X通过这种方式，您可以将结果包装到派生表中，然后对其应用其余的操作。
我想这也是可行的，但现在不能测试：
SELECT DATENAME(MM, CAST([datetime] AS DATE)) + ' '
+ CAST(DAY(CAST([datetime] AS DATE)) AS VARCHAR(2)) AS [DD Month],
SUM(quantity) as total_quantity
FROM Invoice_Itemized ii
INNER JOIN Invoice_Totals it ON it.Invoice_Number = ii.Invoice_Number
WHERE ii.ItemNum = '4011'
AND it.[datetime] > '05/15/2015'
GROUP BY CAST([datetime] AS DATE)页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/30829875
复制相关文章
相似问题
腾讯云开发者
