locked
Basic math RRS feed

  • 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

    (descrip = advil cream)

    (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
    17 advil 10.00
    18 something 5.00

     

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

Answers

  • 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,

                    SUM(CASE WHEN Descrip = ‘Advil’ THEN PRICE ELSE 0 END) SumOfAdvil

    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
    WHERE m.descrip IN('Advil','Cream')
    GROUP BY GROUPING SETS ((p.name,p.patient_id,m.descrip),())
    
    

     


    http://jahaines.blogspot.com/
    • Proposed as answer by Naomi N Tuesday, December 20, 2011 5:51 PM
    • Marked as answer by Bryan Rev 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
    Where m.descrip in('advil','cream')
    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
    Where m.descrip in('advil','cream')
    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
    17 advil 10.00
    18 something 5.00

     


    Bryan Revelant
    • Merged by Naomi N 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

    http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/10b56e3d-aa6a-43e4-9d32-6a37e006e315


    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 17,'advil', 10.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),
    added_date DATETIME,
    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(17,'advil',10.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
    WHERE m.descrip IN('Advil','Cream')
    GROUP BY GROUPING SETS (p.name,())
    
    
    

     


    http://jahaines.blogspot.com/
    • Proposed as answer by Naomi N 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,

                    SUM(CASE WHEN Descrip = ‘Advil’ THEN PRICE ELSE 0 END) SumOfAdvil

    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,

                    SUM(CASE WHEN Descrip = ‘Advil’ THEN PRICE ELSE 0 END) SumOfAdvil

    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
    WHERE m.descrip IN('Advil','Cream')
    GROUP BY GROUPING SETS ((p.name,p.patient_id,m.descrip),())
    
    

     


    http://jahaines.blogspot.com/
    • Proposed as answer by Naomi N Tuesday, December 20, 2011 5:51 PM
    • Marked as answer by Bryan Rev 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)

    SumOfAdvil

    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

    name                SumOfCream          SumOfAdvil
    Betsy J             30.00               60.00
    Bob O'Connor        10.00               20.00
    James Earl Jones    5.00                10.00
    Roy Kres            10.00               20.00

     

     


    Bryan Revelant
    Tuesday, December 20, 2011 5:58 PM