1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

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