Results 1 to 5 of 5

Thread: How to group by a table based on another table

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    66

    How to group by a table based on another table

    I have fee groups and under that fee groups, there are fee types. When there are entries of all the fee types under a group, the group name must be displayed. How can I achieve this?
    Example There is a fee group f1 with feegroupid 1. under that fee group, in another table there are 2 types of fee types ft1,ft2. If both ft1 and ft2 is present in the result table, group them and they should be displayed as f1

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,900

    Re: How to group by a table based on another table

    You join the tables together using an inner join and then you can group by any field in the comined set.
    The best argument against democracy is a five minute conversation with the average voter - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,834

    Re: How to group by a table based on another table

    Do you mean something like this? MS SQL 2005

    Code:
    create table ##FeeGroups(FeeGroupID int, FeeType Char(3))
    insert into ##FeeGroups(FeeGroupID,FeeType) Values(1,'ft1')
    insert into ##FeeGroups(FeeGroupID,FeeType) Values(2,'ft2')
    insert into ##FeeGroups(FeeGroupID,FeeType) Values(3,'ft3')
    
    create table ##FeeTypes(FeeType Char(3))
    insert into  ##FeeTypes(FeeType) Values('ft1')
    insert into  ##FeeTypes(FeeType) Values('ft2')
    insert into  ##FeeTypes(FeeType) Values('ft3')
    insert into  ##FeeTypes(FeeType) Values('ft4')
    
    select FeeType from
    (select 
    case when b.FeeType = 'ft1' or b.FeeType = 'ft2' then 'ft1' 
    else b.feetype
    end as 'FeeType'
    from ##FeeGroups a
    join ##FeeTypes  b on a.Feetype = b.Feetype) as Temp
    group by FeeType
    
    drop table ##FeeGroups
    drop table ##FeeTypes
    Notice ft4 was not matched and is not in the results.
    Last edited by TysonLPrice; Feb 28th, 2014 at 07:32 AM.
    Please remember next time...elections matter!

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    66

    Re: How to group by a table based on another table

    Its something like this:
    First create groups
    create table feegroups(feegroupid int,feegroupname varchar)
    insert feegroups values(1,'SEMESTER1')
    Next:
    create table feetypes(feetypeid int, feegroupid int,feename varchar)
    insert feegroups values(1,1,'FIRSFEE')
    insert feegroups values(2,1,'SECONDFEE')
    insert feegroups values(3,1,'THIRDFEE')
    Note the feegroupid value comes from first table (SEMESTER1'S ID)

    Now while paying fees, the entry goes like this
    create table feespayment(studentid int,feetypeid int)
    insert feespayment values(45,1)
    insert feespayment values(45,2)
    insert feespayment values(45,3)
    -A student with Id 45 has paid all the fees under 'semester1'

    So if a student pays all the 3 feetypes under the feegroup 'SEMESTER1', then while selecting from the feespayment (report), 'SEMESTER1' should be displayed instead of all the feetype names
    like :
    STUDENT45,SEMESTER1

  5. #5
    Addicted Member
    Join Date
    Oct 2008
    Location
    Califorina
    Posts
    235

    Re: How to group by a table based on another table

    Code:
    select distinct(fp.studentid), fg.feegroupname
    from feeGroups as fg inner join feeTypes ft
      on fg.feeGroupID = ft.feeGroupID
    inner join feesPayment fp
      on fp.feeTypeID = ft.feeTypeID 
    where fp.feeTypeID in (1, 2, 3)
     and fg.feegroupid = 1

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width