none
Query needed

    Question

  • declare @temp table (acctnum int, unitnum int)
    insert into @temp (acctnum,unitnum)
    select 101,1 union
    select 101,2 union
    select 101,3 union
    select 102, 1 union
    select 103,1 union
    select 104,1 union
    select 104,3 union
    select 104, 3
    I want to get the acctnum's in more than 1 unit.  BAsed on this table, i need 101 and 104.

    Thanks.


    NSG12

    Friday, April 05, 2013 3:36 PM

Answers

  • Hi -

    Try this:

    declare @temp table (acctnum int, unitnum int)
    insert into @temp (acctnum,unitnum)
    select 101,1 union
    select 101,2 union
    select 101,3 union
    select 102, 1 union
    select 103,1 union
    select 104,1 union
    select 104,3 union
    select 104, 3
    select * from @temp 
    select 
    	t.acctnum 
    from @temp t 
    group by t.acctnum 
    having COUNT(*) > 1


    - will

    Friday, April 05, 2013 3:43 PM
  • declare @temp table (acctnum int, unitnum int)
    insert into @temp (acctnum,unitnum)
    select 101,1 union
    select 101,2 union
    select 101,3 union
    select 102, 1 union
    select 103,1 union
    select 104,1 union
    select 104,3 union
    select 104, 3
    
    select acctnum from @temp
    group by acctnum
    having(count(acctnum)>1)
    
    --Or
    Select acctnum from (select acctnum, count(distinct unitnum) cnt
     from @temp group by acctnum) t
     WHERE cnt>1
    

    Friday, April 05, 2013 3:48 PM

All replies

  • Hi -

    Try this:

    declare @temp table (acctnum int, unitnum int)
    insert into @temp (acctnum,unitnum)
    select 101,1 union
    select 101,2 union
    select 101,3 union
    select 102, 1 union
    select 103,1 union
    select 104,1 union
    select 104,3 union
    select 104, 3
    select * from @temp 
    select 
    	t.acctnum 
    from @temp t 
    group by t.acctnum 
    having COUNT(*) > 1


    - will

    Friday, April 05, 2013 3:43 PM
  • SELECT DISTINCT acctnum
    FROM(SELECT acctnum
    	,unitnum
    	,SUM(unitnum) OVER(PARTITION BY acctnum) UnitNumSum
    		FROM @temp)t
    WHERE t.UnitNumSum>1


    Narsimha

    Friday, April 05, 2013 3:43 PM
  • declare @temp table (acctnum int, unitnum int)
    insert into @temp (acctnum,unitnum)
    select 101,1 union
    select 101,2 union
    select 101,3 union
    select 102, 1 union
    select 103,1 union
    select 104,1 union
    select 104,3 union
    select 104, 3
    
    select acctnum from @temp
    group by acctnum
    having(count(acctnum)>1)
    
    --Or
    Select acctnum from (select acctnum, count(distinct unitnum) cnt
     from @temp group by acctnum) t
     WHERE cnt>1
    

    Friday, April 05, 2013 3:48 PM
  • Thank you all.  The answers helped.

    NSG12

    Friday, April 05, 2013 3:58 PM