时间戳

首页 > 解决方案 > sql子查询中的分组

sql - sql子查询中的分组

问题描述

所以我有一个查询，我试图累积电话和网站的订单计数，但是我意识到当它直接在选择中时，你不能在子查询中使用 GROUP BY 子句，因为它返回多个值。因此我的问题是我在哪里放置子查询以达到相同的结果。

SELECT CAST(a.DateCreated AS DATE), 
        (SELECT count(CAST(DateCreated AS DATE))
         FROM [Sterlingbuild].[dbo].[CustomerOrder]
         WHERE BookingSourceId = 1
         GROUP BY CAST(DateCreated AS DATE)) AS 'Website',
         (SELECT count(CAST(DateCreated AS DATE))
         FROM [Sterlingbuild].[dbo].[CustomerOrder]
         WHERE BookingSourceId = 2
         GROUP BY CAST(DateCreated AS DATE)) AS 'Phone'
  , count(CAST(a.DateCreated AS DATE)) AS 'Total Orders'
  FROM [Sterlingbuild].[dbo].[CustomerOrder] a
  WHERE CustomerOrderStatusId = 7
  AND DepartmentId = 1
  GROUP BY CAST(a.DateCreated AS DATE)
  ORDER BY CAST(a.DateCreated AS DATE)

当我运行此查询时，它可以工作，因此我知道数据是正确的：

  SELECT count(CAST(DateCreated AS DATE))
     FROM [Sterlingbuild].[dbo].[CustomerOrder]
     WHERE BookingSourceId = 1
     AND CustomerOrderStatusId = 7 AND DepartmentId = 1
     GROUP BY CAST(DateCreated AS DATE)

标签： sqlsql-serverdatabasestored-procedures

解决方案

我猜你想要条件聚合：

SELECT CAST(co.DateCreated AS DATE), 
       SUM(CASE WHEN BookingSOurceId = 1 THEN 1 ELSE 0 END) as Website,
       SUM(CASE WHEN BookingSOurceId = 2 THEN 1 ELSE 0 END) as Phone,
       COUNT(*) as Total_Orders
FROM [Sterlingbuild].[dbo].[CustomerOrder] co
WHERE CustomerOrderStatusId = 7 AND DepartmentId = 1
GROUP BY CAST(a.DateCreated AS DATE)
ORDER BY CAST(a.DateCreated AS DATE)

推荐阅读