You must Sign In to post a response.
  • Category: SQL Server

    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

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

    result need.
    drname usedcount
    b 9
    c 11
  • #769727
    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.

    B.Ramana Reddy

  • #769732
    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

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

    drname usedcount
    aa 7
    bb 13

  • #769733
    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

    B.Ramana Reddy

  • Sign In to post your comments