CODEWITHSUNDEEP
sqlsql-servergroup-bysubquery
Ben
Ben

Reputation: 833

Group by a COUNT subquery in SQL Server

would love suggestions on how to achieve my needs as explained below -

Group by a subquery result, example of such a scenario: I got a table of transactionLog, each transaction can be processed multiple times. I have a large statistics query to analyze stuff by attempts count, for example - The total transactions amount grouped by process count.

The query I tried to build in order to achieve the example above was:

SELECT 
    SUM(Amount),
    (SELECT COUNT(*) FROM [db].[dbo].[transactionlog] t2 WHERE t2.OrderGuid = [db].[dbo].[transactionlog].OrderGuid) as 'ProccessCount'
  FROM [db].[dbo].[transactionlog]

  group by 'ProccessCount'

Which throws:

Each GROUP BY expression must contain at least one column that is not an outer reference.

Obviously my real life scenario is a bit wider, this example is only a way to explain what I'm trying to achieve. In real life I have multiple queries like so with different where clause for different types of statistics which I union all of them together to one result set. seems like the union and everything works fine, it just the group by that doesn't work.

Thanks!

~~EDIT - further explanation of my specific situation ~~ Lets say I don't have a field called OrderGuid.

TransactionLog is built as such - 

TransactionLogId (primary key)
Amount
ExtraProcessOfTransactionLogId (default to 0)

Every original transaction has the field ExtraProcessOfTransactionLogId set to 0. Every time a transaction is reprocessed a new transaction is been inserted with ExtraProcessOfTransactionLogId set to the TransactionLog of the original transaction.

I want to achieve the same thing - to sum the amount of all the transactions grouped by the process attempts count.

Sample Data - 

TransactionLogId:1, Amount:100, ExtraProcessOfTransactionLogId :0
TransactionLogId:2, Amount:100, ExtraProcessOfTransactionLogId :0
TransactionLogId:3, Amount:100, ExtraProcessOfTransactionLogId :1

I want to have a query which will result with - 

ProcessAttempts : 1, TotalAmount : 100
ProcessAttempts : 2, TotalAmount : 200

Thanks a lot!

Upvotes: 2

Views: 5577

Answers (2)

Salman Arshad
Salman Arshad

Reputation: 272146

Silly mistaek but this:

group by 'ProccessCount'

Should be:

group by ProccessCount

Right now you're grouping by a string 'ProcessCount' which throws that error, but then you cannot group on something that is calculated in select clause. You need to do something like this:

WITH cte AS (
    SELECT SUM(Amount) AS txn_total, COUNT(*) AS attempts
    FROM transactionlog
    GROUP BY ISNULL(NULLIF(ExtraProcessOfTransactionLogId, 0), TransactionLogId)
)
SELECT attempts, SUM(txn_total) AS sum_txn_total
FROM cte
GROUP BY attempts
ORDER BY attempts

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269973

If I understand correctly, you want two levels of aggregation:

SELECT ProcessCount, SUM(Amount), COUNT(*)
FROM (SELECT SUM(Amount) as Amount, COUNT(*) as ProcessCount
      FROM [db].[dbo].[transactionlog] tl
      GROUP BY IIF(ExtraProcessOfTransactionLogId = 0, TransactionLogId, ExtraProcessOfTransactionLogId)
     ) og
GROUP BY ProcessCount
ORDER BY ProcessCount;

This first aggregates by OrderGuid to get the count and sum for each order. It this aggregates to get the number (and amount) for orders with each number of transactions.

Upvotes: 3

Related Questions