sql子查询中的分组依据

e5nqia27  于 2021-08-13  发布在  Java
关注(0)|答案(1)|浏览(408)

因此,我有一个查询,我试图积累的电话和网站的订单计数,但我意识到你不能使用一个子查询时,它是直接在一个选择,因为它返回一个以上的值组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)
0mkxixxg

0mkxixxg1#

我猜您需要条件聚合:

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)

相关问题