complex sql query counting and grouping

Question

in table LOG i have the following entries

userid     item     role
1          Apple    Buyer
2          Banana   Seller
3          Apple    Buyer
1          Banana   Seller
3          Orange   Buyer
etc

i'm trying to create the following two tables with SQL. table 1:

 item      countUser1Buy      countUser1Sell
 Apple
 Banana
 Orange

AND TAble 2 (here i mean the mean of totals. as in, the mean of the total apples bought

 item      alluserBuyTotalMean      allUserBuyTotalMedian   allUserBuyRange   allUserBuyStandardDev
 Apple
 Banana
 Orange

i think the first should be a variation on

`SELECT `item`, `role`, count(`item`) as count FROM `LOG` GROUP BY `item`

which is close, giving me

item    role   count
Apple   Buyer   1
Banana  Seller  1

but i cant figure out how to make it as i'm trying to get. the second i'm really stumped. thanks

Giorgos Betsos · Accepted Answer · 2016-06-01 19:11:58Z

2

You need conditional aggregation for this:

SELECT `item`, 
       COUNT(CASE WHEN role='Seller' THEN 1 END) AS countUserSell,
       COUNT(CASE WHEN role='Buyer' THEN 1 END) AS countUserBuy  
FROM `LOG` 
GROUP BY `item`

answered Jun 1, 2016 at 19:11

Giorgos Betsos

72.3k10 gold badges69 silver badges103 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shmosel · Accepted Answer · 2016-06-01 19:32:35Z

1

First query:

SELECT item
     , SUM(role = 'Buyer') AS countUser1Buy
     , SUM(role = 'Seller') AS countUser1Sell
  FROM LOG
 WHERE userid = 1
 GROUP BY item;

Second query:

SELECT item
     , AVG(total) AS alluserBuyTotalMean
     , CONCAT(MIN(total), ' - ', MAX(total)) AS allUserBuyRange
     , STDDEV_POP(total) AS allUserBuyStandardDev
  FROM (
       SELECT item
            , COUNT(*) AS total
         FROM LOG
        WHERE role = 'Buyer'
        GROUP BY item, userid
       ) t
 GROUP BY item;

answered Jun 1, 2016 at 19:32

shmosel

51.1k7 gold badges82 silver badges148 bronze badges

Comments

TheGuyThatDoesn'tKnowMuch · Accepted Answer · 2016-06-01 19:17:15Z

1

SELECT
`item`
,SUM(CASE WHEN `userid`= 1 AND `role` = 'Buyer' THEN 1 ELSE 0 END) as countUser1Buy
,SUM(CASE WHEN `userid` = 1 AND `role` = 'Seller' THEN 1 ELSE 0 END) as countUser1Sell
FROM `LOG`
GROUP BY Item

You will need to make a new CASE statement for each userid, but this should work. Results in the following:

item    countUser1Buy   countUser1Sell
Apple   1               0
Banana  0               1
Orange  0               0

answered Jun 1, 2016 at 19:17

TheGuyThatDoesn'tKnowMuch

1,2502 gold badges17 silver badges29 bronze badges

Collectives™ on Stack Overflow

complex sql query counting and grouping

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related