problem with count and sum

Discussion in 'MySQL' started by kvelentz, Feb 21, 2013.

  1. #1
    Hello. I have some difficulty when I use the following query. I have a problem with count and sum.
    The joins are all correct.

    select s.id, s.PromoTitle, count(distinct sc.id),sum(trx.amount)
    from silver s
    join interest i on s.Id = i.silverId
    join customertransaction trx on i.Id = trx.InterestId
    join silvercertificate sc on i.Id = sc.InterestId
    where i.SystemId='silvers.gr' and i.StatusCode in (301,302,312) and sc.StatusCode in (600,601,612,602) and trx.StatusCode IN (401,1001,200,201)
    group by s.id;
    Code (markup):
    The problem is when I use the silvercertificate table although it shows me the correct count(distinct sc.id) it does NOT show me the correct the other table customertransaction sum(trx.amount).
    And unfortunately I have to use 2 queries which after I join.
    there is some problem with count and sum but I can not find them.
    Also the group by I have tried many other variables but it does not work.

    I have tried and other solution with nested select but still it not brings me the correct results
     
    kvelentz, Feb 21, 2013 IP