CODEWITHSUNDEEP
sql
paru80
paru80

Reputation: 93

In Sql, group on Column , but not show in all rows

I am working on a query in database. Say i have a patient which travelled to 3 hospitals. Now i want to add the cost of his journey but want to show it only at his first journey ,the total cost.

file right now is like

Patient Hospital1  cost
A         1        200
A         2        400
A         3        100
B         1        200

I want the output as

Patient Hosptial      Cost
A         1           700
A         2
A         3
B         1           200

Thanks

Upvotes: 0

Views: 52

Answers (3)

MDiesel
MDiesel

Reputation: 2667

If you had the records in a table, called PatientVisits, with the following data:

enter image description here

You could use this query:

SELECT PV.Patient, PV.Hospital, PVGroup.TotalCost
FROM PatientVisits PV
LEFT JOIN (
SELECT Patient, MIN(Hospital) as FirstVisit, SUM(Cost) as TotalCost
FROM PatientVisits
GROUP BY Patient) PVGroup ON
PV.Patient = PVGroup.Patient AND
PV.Hospital = PVGroup.FirstVisit
ORDER BY PV.Patient, PV.Hospital

The results would be:

enter image description here

Upvotes: 0

Amira Bedhiafi
Amira Bedhiafi

Reputation: 1

CREATE TABLE MyTable(Patient varchar(20),Hospital int, Cost int)
INSERT INTO MyTable(Patient,Hospital,Cost) VALUES ('A',1,200),('A',2,400),
('A',3,100),
('B',1,200)

WITH CTE AS (SELECT Patient,SUM(Cost) AS Cost FROM MyTable
GROUP BY Patient)

SELECT M.Patient,M.Hospital,
CASE WHEN ROW_NUMBER() OVER (PARTITION BY M.Patient ORDER BY M.Hospital)=1 THEN CAST(C.Cost AS VARCHAR(255))
ELSE '' END AS Cost FROM CTE AS C
INNER JOIN Mytable AS M ON C.Patient=M.Patient

Patient | Hospital | Cost
:------ | -------: | :---
A       |        1 | 700 
A       |        2 |     
A       |        3 |     
B       |        1 | 200

db<>fiddle here

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269923

You can use window functions:

select t.*,
       (case when row_number() over (partition by patient order by hospital1) = 1
             then sum(cost) over (partition by patient)
        end) as total_cost
from t
order by patient, hospital1;

Upvotes: 1

Related Questions