Category: SQL Server

15 Aug 2018 16:02

Joined:	14/09/2006
Level:	Silver
Points:	161

Points:	3

How to do group by with two tables

HI all,
my two tables are as follows
Ttable UseDr
id drugid usedcount
1 d2 3
2 d3 3
3 d2 5
4 d2 1
5 d3 8

TableDrugDr
drugid drname
d1 a
d2 b
d3 c
d4 d
d5 e

result need.
drname usedcount
b 9
c 11

#769727

15 Aug 2018 19:33

Joined:	28/09/2005
Level:	Gold
Points:	637

Points:	3

I hope you have created the 2 tables with parent and child relation ship like primary key should required on 1st table on DrugId column and Foreign key on 2nd table drugId column by referring 1st table drugid column.

Now use INNER JOIN concept and make grouping on 2 tables and get the desired results

Select DrugDr.drname as drname, UseDr.drugid as usedcount from UseDr INNER JOIN DrugDr ON UseDr.drugid = DrugDr.drugid

Hope the above is useful for your requirement.

Thanks!
B.Ramana Reddy

#769732

17 Aug 2018 11:56

Joined:	14/09/2006
Level:	Silver
Points:	161

Points:	1

thanks for reply ramanna, but there is a small change in this tables. table 1 and table 2 joins with drugid and drugusedid but table 2 drugname will be same for more than one drugusedid (this is used for patient). for example
drug aa used for d1 and d2. Please refer below table
Ttable UseDr
id drugid usedcount
1 d1 3
2 d2 3
3 d3 5
4 d2 1
5 d4 8

TableDrugDr
drugusedid drname
d1 aa
d2 aa
d3 bb
d4 bb
d5 cc

drname usedcount
aa 7
bb 13

#769733

17 Aug 2018 14:36

Joined:	28/09/2005
Level:	Gold
Points:	637

Points:	2

use below query , it will return drug used count as per drug name.

select DrugDr.drname, sum(UseDr.usedcount) from UseDr INNER JOIN DrugDr on DrugDr.drugusedid=UseDr.drugid
group by DrugDr.drname

Thanks!
B.Ramana Reddy

How to do group by with two tables

About dotnetspider.com

Quick Links

General

Services