SQL query assistance

Question

Hello

I've got a table of student payments, screen shot (left). What I need is to return a list of students payments that are >0 when a student has one or more payments. or if a student has only one payment entry and the value is 0, return this single row. screen shot (right). I need a least one row for each student

I've tried Union and Partitioning but can't seem to get it to work are required.

All suggestions much appreciated

Thanks in advance
Ron

Answer 1

Hi @ron barlow
You don't need to use UNION or Partition, just use 'OR' along with subquery in your WHERE filter clause will be fine.
Please check:

SELECT * FROM @tbl A  
WHERE Payment > 0 OR (SELECT COUNT(*) FROM @tbl B WHERE A.StudentID=B.StudentID) = 1  

Also, in this case, you could transfer the logic to: any payment > 0 or Max (payment) =0, like this:

SELECT * FROM @tbl A  
WHERE Payment > 0 OR (SELECT MAX(Payment) FROM @tbl B WHERE A.StudentID=B.StudentID) = 0  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @ron barlow ,

Please try the following solution.

SQL

-- DDL and sample data population, start  
DECLARE @tbl TABLE (StudentID INT, PolicyID INT, Payment DECIMAL(10,2));  
INSERT @tbl (StudentID, PolicyID, Payment) VALUES  
(1, 1000, 100),  
(1, 1001, 0),  
(1, 1005, 150),  
(2, 1009, 0),  
(3, 1006, 90),  
(3, 1008, 175),  
(4, 1007, 110),  
(4, 1003, 0)  
-- DDL and sample data population, end  
  
;WITH rs AS  
(  
	SELECT *   
		, cnt = COUNT(*) OVER (PARTITION BY StudentID ORDER BY StudentID)  
	FROM @tbl  
)  
SELECT * FROM rs  
WHERE cnt > 1 AND rs.Payment > 0  
UNION ALL  
SELECT * FROM rs  
WHERE cnt = 1  
ORDER BY rs.StudentID;  

Output

+-----------+----------+---------+-----+  
| StudentID | PolicyID | Payment | cnt |  
+-----------+----------+---------+-----+  
|         1 |     1000 |  100.00 |   3 |  
|         1 |     1005 |  150.00 |   3 |  
|         2 |     1009 |    0.00 |   1 |  
|         3 |     1006 |   90.00 |   2 |  
|         3 |     1008 |  175.00 |   2 |  
|         4 |     1007 |  110.00 |   2 |  
+-----------+----------+---------+-----+