I'm trying to count multiple columns in a select statement for a class I'm in.

Everything is coming from the same table. I want to know how many times that person paid something and how many times they received something.

There is an ID for each time something is given, but that same ID would be for when someone receives something. I was trying to do the following, but I keep getting Scalar Subquery produced more than one element.

Select Distinct A, Receiverid, Giver, C, D, sum (received amount)
(Select Count(paidid) FROM Table WHERE A ='12345' GROUP BY Giver) as NumberGiven
(Select Count(Receiverid) FROM Table WHERE A ='12345' GROUP BY Receiverid) as NumberReceived
FROM Table
WHERE A = '12345'
Group By A, ReceiverID, Giver, C, D

2 Answers 2


As per my understanding of the problem, you should be able to solve this using window aggregation:

SELECT  -- no DISTINCT needed, as correctly pointed out by Lennart
  A, ReceiverID, Giver, C, D,
  COUNT(*) OVER (PARTITION BY Giver) as NumberGiven,
  COUNT(*) OVER (PARTITION BY ReceiverID) as NumberReceived
  A = '12345'
  A, ReceiverID, Giver, C, D

You are getting the error because you are grouping by Giver in NumberGiven (the same error occurs for NumberReceived). A simple example:

x   y
1   1
1   2
2   3

select count(1) from T group by x

will result in:

2  -- the number of rows for group x=1
1  -- the number of rows for group x=2

Your subqueries probably contain multiple groups, and you, therefore, get more than one count, which leads to your error.

Since I don't know what your table looks like, I can only guess what the query should look like:

Select A, C, D  -- distinct not necessary because of group by
      , sum(received amount) as received_amount
      , count(case when receiver = A then 1 end) as #received 
      , count(case when Giver = A then 1 end) as #given
FROM Table
WHERE A = '12345'
Group By A, C, D

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.