CODEWITHSUNDEEP
sqlsybase
Jean
Jean

Reputation: 429

sum values of a column over distinct values of other column

I'm using sybase database, an extraction of my table would be : 

ClientNumber|ArticlesBought|TotalAllowed
2223        |2             |20
2223        |1             |20
2226        |3             |25
2226        |2             |25
2227        |1             |20

What I need is to calculate the sum of all the articlesBought and devide it by the sum of the total allowed for the distinct clients.

sum(ArticlesBought) = 9 and sum(TotalAllowed)= 65 and not 110

My first question is can I do this in one query? I tried using:

select sum(TotalAllowed)
from myTable
group by ClientNumber

but it returns 40, 45 and 20, which is wrong. Could you please help me ?

Thank you

Upvotes: 0

Views: 501

Answers (2)

Kapil
Kapil

Reputation: 987

         Select my.ClientNumber  ,     my2.TotalArticlesBought/ClientData.TotalAllowedperClient from tblFabSource my
    inner join (select sum(Distinct TotalAllowed)as     TotalAllowedperClient,ClientNumber from myTable group by ClientNumber)    
    ClientData on ClientData .ClientNumber=my.ClientNumber  
    inner join (select sum(ArticlesBought)as TotalArticlesBought from     myTable ) my2   
      on my2.TotalArticlesBought>0

Upvotes: 1

beejm
beejm

Reputation: 2481

select SUM(articlesbought), SUM(DISTINCT totalallowed) from tablename

you can use a distinct on the sum of totalallowed

Upvotes: 0

Related Questions