CODEWITHSUNDEEP
sqlsql-serverperformance
Maxim Polishchuk
Maxim Polishchuk

Reputation: 334

Calculate COUNT in GROUP BY SQL query

I have query to calculate total amount by one product item below:

SELECT p.[Id], COUNT(o.[Id]), SUM(o.[Amount])
FROM [dbo].[Order] o
    INNER JOIN [dbo].[Product] p ON p.[Id] = o.[ProductId]
GROUP BY p.[Id]

In this case, COUNT(o.[Id]) is number of unique orders by one product item.

But instead of COUNT(o.[Id]) I would like to calculate number of orders using next formula:

COUNT(o.[OwnerId]) -- where o.[OwnerId] IS NOT NULL
+
COUNT(o.[Id]) -- where o.[OwnerId] IS NULL

Could I implement it in one query? What solution will be better for performance?

UPDATE:

I am so sorry for my fast finish. Mahmoud Gamal provides nice example, but it calculates a bit else.

SELECT
    p.[Id],
    COUNT(DISTINCT CASE WHEN o.[OwnerId] IS NOT NULL THEN o.[OwnerId] ELSE NULL END) AS [NumberOfOrderOwners],
    SUM(CASE WHEN o.[OwnerId] IS NULL THEN 1 ELSE 0 END) AS [NumberOfAnonimousOwners],
    SUM(o.[Amount])
FROM [dbo].[Order] o
    INNER JOIN [dbo].[Product] p ON p.[Id] = o.[ProductId]
GROUP BY p.[Id]

Query above calculates as I need, but estimated cost is increased in twice because I use DISTINCT inside of COUNT.

Could anybody help me to improve my query?

Upvotes: 1

Views: 1644

Answers (1)

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79959

Try this:

SELECT 
  p.[Id],
  SUM(CASE WHEN o.[OwnerId] IS NOT NULL THEN 1 ELSE 0 END) +
  SUM(CASE WHEN o.[Id]      IS NULL     THEN 1 ELSE 0 END) AS Total
FROM [dbo].[Order] o
INNER JOIN [dbo].[Product] p ON p.[Id] = o.[ProductId]
GROUP BY p.[Id]

Upvotes: 2

Related Questions