Home > database >  SQL divide count grouped by the same value from different columns
SQL divide count grouped by the same value from different columns

Time:07-28

So I have a table with two rows containing names and some other stuff, these two columns can contain the same name:

Buyer Seller RegionID ...
John Lina 1
Lina Kajsa 2
John Conny 5
Kajsa Conny 3
John Erik 2
Kajsa Conny 1
John Conny 2
John John 1
John Conny 1
Kajsa David 1
David David 1

Lets call this table Store, I can do queries like this:

select Buyer, count(*) from Store where RegionID=1 group by Buyer

to get how many buys a person has made, and can make similar query for Seller by replacing Buyer with Seller.

These queries would give me something like this:

Buyer count(*)
John 3
Kajsa 2
David 1
Seller count(*)
Lina 1
Conny 2
John 2
David 2

So what I want to do is to make a query that list each unique name in one column, and the buyer count divided by the seller count in a second column. But I can't figure out how to do that.

I basically want to be able display all of Johns buys divided by his sells.

CodePudding user response:


SELECT clinetname,
       Sum(buyercount) / Sum(sellercount)
FROM   (SELECT buyer    AS ClinetName,
               Count(1) AS BuyerCount,
               0        AS SellerCount
        FROM   Store
        WHERE  regionid = 1
        GROUP  BY buyer
        UNION ALL
        SELECT seller   AS ClinetName,
               0        AS BuyerCount,
               Count(1) AS SellerCount
        FROM   Store
        WHERE  regionid = 1
        GROUP  BY seller) a
GROUP  BY clinetname
HAVING Sum(sellercount) > 0; 

CodePudding user response:

Does john both buyer and seller.

  • Related