Sql-server – Trying to Count Multiple Columns SQL

counterrorssql server

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

Best Answer

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
NumberReceived
FROM Table
WHERE A = '12345'
Group By A, C, D
Related Question