SQL divide count grouped by the same value from different columns-CodePudding

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.