# Basic math

• ### Question

• I want  the total by person of everyone taking cream and everyone taking advil .

Database

Table = patient  visit  medicine

patient - name

medicine - descrip price

(price advil is 10 cream is 5)

How could I find the  total by person of everyone taking cream and everyone taking advil?

Of course this is a practice database that I made dummy information below.

Bryan Revelant

database

table names patient visit medicine

Column name

patient_id name added_date LastSeenDate lastreasonOfVisit CountOfVisits
2 Betsy J 2011-12-11 14:57:43.023 2011-12-13 10:07:17.750 Dec 16 2011 11:13AM 4
3 Bob O'Connor 2011-12-11 14:57:43.023 2011-12-06 14:59:25.943 Dec  6 2011  2:59PM 2
5 James Earl Jones 2011-12-11 14:57:43.023 2011-12-12 10:33:07.320 Dec 12 2011 10:33AM 1
16 b rev 2011-12-12 09:39:00.563 NULL NULL 3
23 k rev 2011-12-12 10:25:32.640 NULL NULL NULL
25 fred clause 2011-12-12 10:35:17.737 NULL NULL NULL
26 tim 2011-12-12 10:38:42.757 NULL NULL NULL
27 fred S 2011-12-12 10:38:42.757 NULL NULL NULL
28 Roy Kres 2011-12-14 11:43:34.320 2011-12-14 11:45:27.197 Dec 14 2011  3:19PM 1

visit

visit_id    staff_id      reason date_visit     Patient_id      medicine_id      surgery_id
1  4 2011-12-11 14:59:25.907 4 5 1
2 2 head ache 2011-11-21 14:59:25.943 2 4 NULL
3 3 cold 2011-12-01 14:59:25.907 3 5 NULL

medicine

medicine_id descrip price
1 ear drops 50.00
2 fungal cream 50.00
3 bandaides 2.00
4 cream 5.00
5 eye drops 20.00
6 touthpaste 3.00
15 foot cream 12.00
18 something 5.00

• Edited by Monday, December 19, 2011 8:05 PM
Monday, December 19, 2011 7:15 PM

• This does work however   You didn’t get it by patient.  Your query would return 1 record for each medicine, we need by patient by specific medicine. I think that it may be written like this

SELECT Patient_ID,

SUM(CASE WHEN Descrip = ‘CREAM’ THEN Price ELSE 0 END) SumOfCream,

FROM Patient_Visit pv

INNER JOIN Medicine m on m.medicineID = pv.MedicineID

GROUP BY PatientID

Bryan Revelant

Bryan,

It is grouped by Patient.  I am grouping by p.name which is the patient name.  You can add patient id to the group by clause and to the grouping set, but it is grouped per patient and each sum is grouped by each medicine with a case expression.  You can verify this by changing one of the insert statements to be a duplicate patient and then change the medicine.

If you need more than one row returned, you dont need a case expression in the select list.

```SELECT
CASE WHEN GROUPING(p.name)=1 THEN 'Total' ELSE p.name END AS Patient,
m.descrip as MedicineDesc,
COUNT(*) AS Total
FROM @Patient p
INNER JOIN @visit v
ON p.patient_id = v.Patient_id
INNER JOIN @medicine m
ON v.medicine_id = m.medicine_id
GROUP BY GROUPING SETS ((p.name,p.patient_id,m.descrip),())

```

http://jahaines.blogspot.com/
• Proposed as answer by Tuesday, December 20, 2011 5:51 PM
• Marked as answer by Tuesday, December 20, 2011 5:58 PM
Tuesday, December 20, 2011 5:45 PM

### All replies

• Try this.It will be easy to answer if you post the DDL of the tables with sample data.

```select p.name,m.descrip,SUM(m.price) from
Patient p join Medicine m on  p.name=m.name
Group by p.name,m.descrip
```

Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
Monday, December 19, 2011 7:26 PM
• Hi Bryan,

Please post your table DDL with some sample data. We may be able to help in this case.

Thanks,

Janos

There are 10 type of people. Those who understand binary and those who do not.
My Blog
Monday, December 19, 2011 7:28 PM
• Try This.

```select p.name,m.descrip,SUM(m.price) from
visit v join
Patient p on v.Patient_id=p.Patient_id
join Medicine m on  v.medicine_id=m.medicine_id
Group by p.name,m.descrip
```

Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
Monday, December 19, 2011 8:12 PM
• I want  to find the total by person of everyone taking cream and everyone taking advil .

I want to get this information using a Case statment like this one --- but this doesnt work

select (price + price),

case when descrip = 'cream' THEN 1

when descrip = 'advil' THEN 2

else 0

end as Price

from medicine

Database information below...

Column name

Patient

patient_id name added_date LastSeenDate lastreasonOfVisit CountOfVisits
2 Betsy J 2011-12-11 14:57:43.023 2011-12-13 10:07:17.750 Dec 16 2011 11:13AM 4
3 Bob O'Connor 2011-12-11 14:57:43.023 2011-12-06 14:59:25.943 Dec  6 2011  2:59PM 2
5 James Earl Jones 2011-12-11 14:57:43.023 2011-12-12 10:33:07.320 Dec 12 2011 10:33AM 1
16 b rev 2011-12-12 09:39:00.563 NULL NULL 3
23 k rev 2011-12-12 10:25:32.640 NULL NULL NULL
25 fred clause 2011-12-12 10:35:17.737 NULL NULL NULL
26 tim 2011-12-12 10:38:42.757 NULL NULL NULL
27 fred S 2011-12-12 10:38:42.757 NULL NULL NULL
28 Roy Kres 2011-12-14 11:43:34.320 2011-12-14 11:45:27.197 Dec 14 2011  3:19PM 1

visit

visit_id    staff_id      reason date_visit     Patient_id      medicine_id      surgery_id
1  4 2011-12-11 14:59:25.907 4 5 1
2 2 head ache 2011-11-21 14:59:25.943 2 4 NULL
3 3 cold 2011-12-01 14:59:25.907 3 5 NULL

medicine

medicine_id descrip price
1 ear drops 50.00
2 fungal cream 50.00
3 bandaides 2.00
4 cream 5.00
5 eye drops 20.00
6 touthpaste 3.00
15 foot cream 12.00
18 something 5.00

Bryan Revelant
• Merged by Tuesday, December 20, 2011 2:54 PM Same topic
Tuesday, December 20, 2011 2:27 PM
• Can you post the sample output format?

You have posted this question already

Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer
Tuesday, December 20, 2011 2:41 PM
• Not sure exactly what you want. Also, the sample data doesn't have taking advil. (And why list advil as opposed to ibuprofen?)

Nonetheless, here's a guess:

```WITH
Patient(patient_id, name, added_date, LastSeenDate, lastreasonOfVisit, CountOfVisits)
AS
(
SELECT 02, 'Betsy J',		'2011-12-11 14:57:43.023','2011-12-13 10:07:17.750',	'Dec 16 2011 11:13AM',	4	UNION ALL
SELECT 03, 'Bob O''Connor',	'2011-12-11 14:57:43.023','2011-12-06 14:59:25.943',	'Dec  6 2011  2:59PM',	2	UNION ALL
SELECT 05, 'James Earl Jones',	'2011-12-11 14:57:43.023','2011-12-12 10:33:07.320',	'Dec 12 2011 10:33AM',	1	UNION ALL
SELECT 16, 'b rev',		'2011-12-12 09:39:00.563', NULL,			NULL,			3	UNION ALL
SELECT 23, 'k rev',		'2011-12-12 10:25:32.640', NULL,			NULL,			NULL	UNION ALL
SELECT 25, 'fred clause',	'2011-12-12 10:35:17.737', NULL,			NULL,			NULL	UNION ALL
SELECT 26, 'tim',		'2011-12-12 10:38:42.757', NULL,			NULL,			NULL	UNION ALL
SELECT 27, 'fred S',		'2011-12-12 10:38:42.757', NULL,			NULL,			NULL	UNION ALL
SELECT 28, 'Roy Kres',		'2011-12-14 11:43:34.320', '2011-12-14 11:45:27.197',	'Dec 14 2011  3:19PM',	1
),
visit(visit_id, staff_id, reason, date_visit, Patient_id, medicine_id, surgery_id)
AS
(
SELECT 1, 4, NULL,		'2011-12-11 14:59:25.907', 4, 5, 1	UNION ALL
SELECT 2, 2, 'head ache',	'2011-11-21 14:59:25.943', 2, 4, NULL	UNION ALL
SELECT 3, 3, 'cold',		'2011-12-01 14:59:25.907', 3, 5, NULL
),
medicine(medicine_id, descrip, price)
AS
(
SELECT 01, 'ear drops',	50.00 UNION ALL
SELECT 02, 'fungal cream',	50.00 UNION ALL
SELECT 03, 'bandaides',	02.00 UNION ALL
SELECT 04, 'cream',		05.00 UNION ALL
SELECT 05, 'eye drops',	20.00 UNION ALL
SELECT 06, 'touthpaste',	03.00 UNION ALL
SELECT 15, 'foot cream',	12.00 UNION ALL
SELECT 17, 'advil',		10.00 UNION ALL
SELECT 18, 'something',	05.00
)
SELECT
Patient.name,
SUM(medicine.price)
FROM
Patient,
visit,
medicine
WHERE
visit.Patient_id	= Patient.patient_id
AND	medicine.medicine_id	= visit.medicine_id
GROUP BY
Patient.name
HAVING
COUNT(CASE WHEN medicine.descrip IN('advil', 'cream') THEN 1 END) > 0;
```

Tuesday, December 20, 2011 2:55 PM
• Hi,

try below query. Let me know in case of any issue.

If OBJECT_ID('Tempdb..#medicine') IS NOT NULL DROP TABLE #medicine
If OBJECT_ID('Tempdb..#Patient') IS NOT NULL DROP TABLE #Patient
If OBJECT_ID('Tempdb..#visit') IS NOT NULL DROP TABLE #visit

Create Table #Patient(patient_id Int,name Varchar(50),added_date DateTime,LastSeenDate DateTime,lastreasonOfVisit Varchar(50),CountOfVisits Int)

Insert Into #Patient
Select 2,'Betsy J','2011-12-11 14:57:43.023','2011-12-13 10:07:17.750','Dec 16 2011 11:13AM',4 Union All
Select 3,'Bob O Connor','2011-12-11 14:57:43.023','2011-12-06 14:59:25.943','Dec  6 2011  2:59PM',2 Union All
Select 5,'James Earl Jones','2011-12-11 14:57:43.023','2011-12-12 10:33:07.320','Dec 12 2011 10:33AM',1 Union All
Select 16,'b rev','2011-12-12 09:39:00.563',NULL,NULL,3 Union All
Select 23,'k rev','2011-12-12 10:25:32.640',NULL,NULL,NULL Union All
Select 25,'fred clause','2011-12-12 10:35:17.737',NULL,NULL,NULL Union All
Select 26,'tim','2011-12-12 10:38:42.757',NULL,NULL,NULL Union All
Select 27,'fred S','2011-12-12 10:38:42.757',NULL,NULL,NULL Union All
Select 28,'Roy Kres','2011-12-14 11:43:34.320','2011-12-14 11:45:27.197','Dec 14 2011  3:19PM',1

Create Table #visit(visit_id Int ,staff_id Int ,reason Varchar(50),date_visit Datetime,Patient_id Int,medicine_id Int,surgery_id Int)
Insert Into #visit
Select 1,4,'','2011-12-11 14:59:25.907',4,5,1 Union All
Select 2,2,'head ache','2011-11-21 14:59:25.943',2,4,NULL Union All
Select 3,3,'cold','2011-12-01 14:59:25.907',3,5, NULL

Create Table #medicine(medicine_id Int,descrip Varchar(50),price Money)
Insert Into #medicine
Select 1,'ear drops', 50.00 Union All
Select 2,'fungal cream', 50.00 Union All
Select 3,'bandaides', 2.00 Union All
Select 4,'cream', 5.00 Union All
Select 5,'eye drops', 20.00 Union All
Select 6,'touthpaste', 3.00 Union All
Select 15,'foot cream', 12.00 Union All
Select 18,'something', 5.00

Select P.name,SUM(price) As Total,M.descrip
From #Patient P
Join #visit V on V.Patient_id=P.patient_id
Join #medicine M ON M.medicine_id=V.medicine_id
Group By P.name,M.descrip

Cheers!! Sumit
Tuesday, December 20, 2011 3:11 PM
• It is unclear what you want your results to look like.  Here is an example that pivots the totals per patient and provides a total row at the bottom.  If this is not what you are looking for, please provide expected results.

```declare @Patient table(
patient_id INT,
name VARCHAR(75),
LastSeenDate DATETIME,
lastreasonOfVisit VARCHAR(MAX),
CountOfVisits INT
);

INSERT INTO @Patient VALUES(2, 'Betsy J','2011-12-11 14:57:43.023','2011-12-13 10:07:17.750','Dec 16 2011 11:13AM',4);
INSERT INTO @Patient VALUES(3, 'Bob O''Connor','2011-12-11 14:57:43.023',' 2011-12-06 14:59:25.943','Dec  6 2011  2:59PM',2);
INSERT INTO @Patient VALUES(5, 'James Earl Jones','2011-12-11 14:57:43.023',' 2011-12-12 10:33:07.320','Dec 12 2011 10:33AM',1);
INSERT INTO @Patient VALUES(16, 'b rev','2011-12-12 09:39:00.563',NULL, NULL, 3);
INSERT INTO @Patient VALUES(23, 'k rev','2011-12-12 10:25:32.640',NULL, NULL, NULL);
INSERT INTO @Patient VALUES(25, 'fred clause','2011-12-12 10:35:17.737',NULL, NULL, NULL);
INSERT INTO @Patient VALUES(26, 'tim','2011-12-12 10:38:42.757',NULL, NULL, NULL);
INSERT INTO @Patient VALUES(27, 'fred S','2011-12-12 10:38:42.757',NULL, NULL, NULL);
INSERT INTO @Patient VALUES(28,' Roy Kres','2011-12-14 11:43:34.320','2011-12-14 11:45:27.197','Dec 14 2011  3:19PM',1);

DECLARE @visit TABLE(
visit_id INT,
staff_id INT,
reason VARCHAR(MAX),
date_visit DATETIME,
Patient_id INT,
medicine_id INT,
surgery_id INT
);

INSERT INTO @visit VALUES(1,4,NULL, '2011-12-11 14:59:25.907',4,5,1);
INSERT INTO @visit VALUES(2,2,'head ache','2011-11-21 14:59:25.943',2,4,NULL);
INSERT INTO @visit VALUES(3,3,'cold','2011-12-01 14:59:25.907',3,5,NULL);
INSERT INTO @visit VALUES(4,1,'pain','2011-12-01 14:59:25.907',28,17,NULL);
INSERT INTO @visit VALUES(5,10,'more pain','2011-12-15 14:59:25.907',28,17,NULL);

DECLARE @medicine TABLE(
medicine_id INT,
descrip VARCHAR(150),
price NUMERIC(9,2)
);

INSERT INTO @medicine VALUES(1,'ear drops',50.00);
INSERT INTO @medicine VALUES(2,'fungal cream',50.00);
INSERT INTO @medicine VALUES(3,'bandaides',2.00);
INSERT INTO @medicine VALUES(4,'cream',5.00);
INSERT INTO @medicine VALUES(5,'eye drops',20.00);
INSERT INTO @medicine VALUES(6,'touthpaste',3.00);
INSERT INTO @medicine VALUES(15,'foot cream',12.00);
INSERT INTO @medicine VALUES(18,'something',5.00);

SELECT
CASE WHEN GROUPING(p.name)=1 THEN 'Total' ELSE p.name END AS Patient,
SUM(CASE WHEN m.descrip = 'Advil' THEN 1 ELSE 0 END) AS Advil,
SUM(CASE WHEN m.descrip = 'Cream' THEN 1 ELSE 0 END) AS Cream,
COUNT(*) AS Total
FROM @Patient p
INNER JOIN @visit v
ON p.patient_id = v.Patient_id
INNER JOIN @medicine m
ON v.medicine_id = m.medicine_id
GROUP BY GROUPING SETS (p.name,())

```

http://jahaines.blogspot.com/
• Proposed as answer by Tuesday, December 20, 2011 3:15 PM
Tuesday, December 20, 2011 3:12 PM
• This does work however   You didn’t get it by patient.  Your query would return 1 record for each medicine, we need by patient by specific medicine. I think that it may be written like this

SELECT Patient_ID,

SUM(CASE WHEN Descrip = ‘CREAM’ THEN Price ELSE 0 END) SumOfCream,

FROM Patient_Visit pv

INNER JOIN Medicine m on m.medicineID = pv.MedicineID

GROUP BY PatientID

Bryan Revelant
Tuesday, December 20, 2011 4:39 PM
• This does work however   You didn’t get it by patient.  Your query would return 1 record for each medicine, we need by patient by specific medicine. I think that it may be written like this

SELECT Patient_ID,

SUM(CASE WHEN Descrip = ‘CREAM’ THEN Price ELSE 0 END) SumOfCream,

FROM Patient_Visit pv

INNER JOIN Medicine m on m.medicineID = pv.MedicineID

GROUP BY PatientID

Bryan Revelant

Bryan,

It is grouped by Patient.  I am grouping by p.name which is the patient name.  You can add patient id to the group by clause and to the grouping set, but it is grouped per patient and each sum is grouped by each medicine with a case expression.  You can verify this by changing one of the insert statements to be a duplicate patient and then change the medicine.

If you need more than one row returned, you dont need a case expression in the select list.

```SELECT
CASE WHEN GROUPING(p.name)=1 THEN 'Total' ELSE p.name END AS Patient,
m.descrip as MedicineDesc,
COUNT(*) AS Total
FROM @Patient p
INNER JOIN @visit v
ON p.patient_id = v.Patient_id
INNER JOIN @medicine m
ON v.medicine_id = m.medicine_id
GROUP BY GROUPING SETS ((p.name,p.patient_id,m.descrip),())

```

http://jahaines.blogspot.com/
• Proposed as answer by Tuesday, December 20, 2011 5:51 PM
• Marked as answer by Tuesday, December 20, 2011 5:58 PM
Tuesday, December 20, 2011 5:45 PM
• I think i may have phrased my question a bit wrong.... Although I really like your answer and what your search produced. I was looking for this.

SELECT name

,

SUM(CASE WHEN Descrip = 'CREAM' THEN Price ELSE 0 END) SumOfCream

,

SUM(CASE WHEN Descrip = 'Advil' THEN PRICE ELSE 0 END)

FROM

patient

p

INNER JOIN visit v on p.patient_id = v.

patient_id

inner join medicine m on m.medicine_id = m.

medicine_id

group by name

Which produces this