Results 1 to 3 of 3
  1. #1
    Join Date
    May 2017
    Posts
    2

    Unanswered: Sybase Group by clause not giving expected Result

    Hi Team,

    I need your help on below code, Im having one table #MyBank which has 20 records, I want to sum up TotalAmount and create combined StampId if DebitDate and InvoiceType are all same. Please refer the below code snippet. In the 20 records there are 4 combination which have DebitDate and InvoiceType are all same. When i run the query the expected result should be four records. But the Problem is the column InvoiceGroupID is not getting grouped properly and its giving more records like 13 records.

    Can someone please help me on the below query so that i could get exact expected results (4 sets)

    select Distinct
    InvoiceGroupID,
    MsgID,
    IsFurikomiPrinted,
    'Combined' StampId,
    InvoiceType,
    BeneficiaryBank,
    BeneficiaryBranch,
    BeneficiaryAccType,
    BeneficiaryAccNo,
    BeneficiaryName,
    BeneficiaryNamexyz,
    Sum(TotalAmount)TotalAmount,
    RemitterName,
    RemitterNamexyz,
    DebitDate,
    Address,'' "Phone"
    from #MyBank
    group by DebitDate having count(InvoiceType+DebitDate) >1

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,369
    Provided Answers: 1
    Your grouping is inconsistent

    This will be the same as summing the amount per group and then joining that to the rest of your data e.g.
    Code:
    --test data
    SELECT * INTO #t1 FROM (SELECT
    'ABC',1,  10.00 UNION ALL SELECT 
    'ABC',1,  20.00 UNION ALL SELECT 
    'ABC',2,  40.00 UNION ALL SELECT 
    'ABC',2,  70.00 UNION ALL SELECT 
    'DEF',1, 100.00 UNION ALL SELECT 
    'DEF',1, 200.00 UNION ALL SELECT 
    'DEF',2, 400.00 UNION ALL SELECT 
    'DEF',2, 700.00 )T(a,b,c)
    
    --Query1: inconsistent grouping
    SELECT a,b,c, sumc=sum(c)
    FROM #t1
    GROUP BY a
    
    --Query2: the same result as above
    SELECT t1.a,t1.b,t1.c,t2.sumc
    FROM #t1 t1
    JOIN (SELECT a, sumc=sum(c) FROM #t1 GROUP BY a) t2
      ON t2.a=t1.a
    
    DROP TABLE #t1
    The output will look like this
    Code:
    a    b    c      sumc    
    ---- ---- ------ --------
    ABC     1  10.00   140.00
    ABC     1  20.00   140.00
    ABC     2  40.00   140.00
    ABC     2  70.00   140.00
    DEF     1 100.00  1400.00
    DEF     1 200.00  1400.00
    DEF     2 400.00  1400.00
    DEF     2 700.00  1400.00
    It can become even more confusing if you add a where condition it only applies to the group by (the t2 table in my 2nd query)

  3. #3
    Join Date
    May 2017
    Posts
    2

    Group by

    Thank you for your suggestion.

Posting Permissions

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