英文:
group by in queries that contains subqueries
问题
如何按customerId分组以下查询：
select
(SELECT SUM(Purchase) FROM Customers where type in (17,21)) as 'Purchase'
,(SELECT SUM(Point) FROM Customers) as 'Point'
FROM CUSTOMERS
GROUP BY Customers.CustomerID
英文:
How can I group by the following query by customerId
select
(SELECT SUM(Purchase) FROM Customers where type in (17,21)) as 'Purchase'
,(SELECT SUM(Point) FROM Customers) as 'Point'
FROM CUSTOMERS
GROUP BY Customers.CustomerID
答案1
得分: 4
看起来你只是想要条件聚合，你可以在SUM内部使用CASE WHEN...来实现。
SELECT
c.CustomerId
SUM(CASE WHEN c.type IN (17, 21) THEN c.Purchase END) AS Purchase,
SUM(c.Point) AS Point
FROM CUSTOMERS c
GROUP BY
c.CustomerID;
请注意使用表别名使其更易读，并且只在必要时使用[]来引用列名，不要使用''。
英文:
Looks like you just want conditional aggregation, which you can do by placing CASE WHEN... inside a SUM.
SELECT
c.CustomerId
SUM(CASE WHEN c.type IN (17, 21) THEN c.Purchase END) AS Purchase,
SUM(c.Point) AS Point
FROM CUSTOMERS c
GROUP BY
c.CustomerID;
Note the use of a table alias to make it more readable, and do not use '' to quote column names, only [] and only where necessary.
答案2
得分: 0
更改查询为：
SELECT
[CustomerID] as 'CustomerID'
, SUM([Purchase]) as 'Purchase'
, SUM([Point]) as 'Point'
FROM [CUSTOMERS]
WHERE [type] in (17,21)
GROUP BY [Customers].[CustomerID]
这将为您提供：
CustomerID Purchase Point
1 246 3
2 23434 7
英文:
Remove the From Customers in the Selects and Move the Where to the root of the query
Change your query to
SELECT
[CustomerID] as 'CustomerID'
, SUM([Purchase]) as 'Purchase'
, SUM([Point]) as 'Point'
FROM [CUSTOMERS]
WHERE [type] in (17,21)
GROUP BY [Customers].[CustomerID]
This will give you
CustomerID Purchase Point
1 246 3
2 23434 7
答案3
得分: -1
如果您想要获取每位客户的总_Purchase_和总_Point_，那么您需要在_Purchase_子查询中以及在主查询中包括CustomerId。
关于Group By需要记住的关键一点是，所有非聚合列都必须包含在其中。因此，在您的情况下，您需要按_Purchase_分组。
您的最终查询将如下所示：
select c.CustomerId,
(SELECT SUM(Purchase) FROM Customers where type in (17,21) and CustomerId = c.CustomerId) as 'Purchase',
SUM(Point) as 'Point'
FROM CUSTOMERS c
GROUP BY c.CustomerID, Purchase
英文:
If you're trying to get the total Purchase and total Points per customer, then you need to include the CustomerId in the Purchase sub-query as well as in your main query.
A key thing to remember regarding Group By is that all non-aggregate columns have to be included in it. Therefore in your case, you'd have to group by the Purchase as well.
Your final query would look like:
<pre>
select c.CustomerId,
(SELECT SUM(Purchase) FROM Customers where type in (17,21) and CustomerId = c.CustomerId) as 'Purchase'
, SUM(Point) as 'Point'
FROM CUSTOMERS c
GROUP BY c.CustomerID, Purchase
</pre>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论