Help me help me

Other SQL Server Topics
1

I have a statement. i want find out first_name, last_name customer who have sum payment for staff 2 > 110 but statement is error :
subquery has too many columns
LINE 3: WHERE customer_id IN
This is my query :
SELECT first_name, last_name
FROM customer
WHERE customer_id IN
( SELECT customer_id, SUM(amount)
FROM payment
WHERE staff_id =2
GROUP BY payment.customer_id
HAVING SUM(amount) > 110 );
Please help me a little bit details. i try using INNER JOIN but it doesn't as well

2

Hi.
Without a script to replicate I go by imagination.

;WITH CTE AS
(
		SELECT
			customer_id
			, SUM(amount) as total_amount
		FROM
			payment
		WHERE
			staff_id = 2
		GROUP BY
			payment.customer_id
		HAVING
			SUM(amount) > 110
)
SELECT
	first_name
	, last_name
FROM
	customer
INNER JOIN CTE ON
	CTE.customer_id = customer.customer_id
3

Thanks you.
I used this query and this run.
SELECT first_name, last_name
FROM customer
WHERE EXISTS
(SELECT DISTINCT customer_id,SUM(amount)
FROM payment
WHERE staff_id =2 AND customer.customer_id = payment.customer_id
GROUP BY customer_id
HAVING SUM(amount)> 110);

4

Sure, it works now, but it might not work in the future.
Have you tried my solution? It does not work?
Thank you.

1 Like
5

Thanks you so much

6

The problem here is that you are trying to return 2 columns to the IN statement. You need to return a single column to be used for the IN statement.

You can fix that easily by remove the SUM(amount) from the SELECT - as it isn't needed.

This works because all it is doing is checking for the existence - it can also be rewritten to remove the DISTINCT and the columns since those don't matter.

This could also be done using a JOIN - if, for example - you wanted total payments for each customer:

SELECT c.customer_id
     , c.first_name
     , c.last_name
     , total_payments = SUM(p.Amount)
  FROM dbo.Customer     AS c
 INNER JOIN dbo.Payment AS p ON p.customer_id = c.customer_id
 WHERE p.staff_id = 2
 GROUP BY
       c.customer_id
     , c.first_name
     , c.last_name
HAVING SUM(p.Amount) > 110;
1 Like
7

Thanks you. This is useful