How to add averages w/ sum (case when...) in MySQL?

Question

Im working on an SQL query that have almost where I want it. I just want to add two more columns for the average submissions for each of the "attributes_share_lemon" and "attribites_not_shared_lemon".

Here is the SQL fiddle that I was working on here:

http://sqlfiddle.com/#!9/6ea5d8/8

Here is the create and the insert:

CREATE TABLE `fruits` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `fruit` varchar(11) DEFAULT NULL,
  `fruit_attribute` varchar(11) DEFAULT '',
  `submissions` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO `fruits` (`id`, `fruit`, `fruit_attribute`, `submissions`)
VALUES
    (1,'Orange','tough peel',59),
    (2,'Lemon','tough peel',70),
    (3,'Orange','citrus',100),
    (4,'Orange','juice',90),
    (5,'Lemon','juice',75),
    (6,'Lemon','tart',35),
    (7,'Lemon','citurs',65),
    (8,'Orange','breakfast',110),
    (9,'Lemon','lemonaid',120),
    (10,'Orange','florida',50);

My query:

SELECT ft1.fruit, ft1.submissions,   
    SUM(CASE WHEN ft2.fruit_attribute IS NOT NULL THEN 1 ELSE 0 END) As attributes_shared_lemon,
    SUM(CASE WHEN ft2.fruit_attribute IS NULL THEN 1 ELSE 0 END) As attributes_not_shared_lemon
FROM fruits ft1 LEFT JOIN
(
    SELECT ft.fruit, ft.fruit_attribute, ft.submissions
    FROM fruits ft
    WHERE ft.fruit = 'Lemon'
) ft2
ON ft1.fruit_attribute = ft2.fruit_attribute
GROUP BY ft1.fruit;

CURRENT OUTPUT

|  fruit | submissions | attributes_shared_lemon | attributes_not_shared_lemon |
|--------|-------------|-------------------------|-----------------------------|
|  Lemon |          70 |                       5 |                           0 |
| Orange |          59 |                       2 |                           3 |

So what is the question? What is wrong? What is your expected result? — Juan Carlos Oropeza, Oct 15 '15 at 19:20
Second sentence: I just want to add two more columns for the average submissions for each of the "attributes_share_lemon" and "attribites_not_shared_lemon". — lcm, Oct 15 '15 at 19:35
Maybe slight sarcasm, but very far from an insult. Nothing like `salty` that was much stronger word. Dont you think I was trying to help? Even when your question was good I will offer some reading material may help you prepare a better question next time. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ and [**Need an Answer? Actually, No ... You Need a Question**](http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx) — Juan Carlos Oropeza, Oct 15 '15 at 21:42

Gordon Linoff · Accepted Answer · 2015-10-15T21:40:06.823

1

Your query looks ok. But, you don't need the subquery, so a simpler version is:

SELECT ft.fruit,   
       COUNT(ftl.fruit_attribute) As attributes_shared_lemon,
       SUM(ftl.fruit_attribute IS NULL) As attributes_not_shared_lemon
FROM fruits ft LEFT JOIN
     fruits ftl
     ON ft.fruit_attribute = ftl.fruit_attribute and ftl.fruit = 'Lemon'
GROUP BY ft.fruit;

I removed the submissions column, because it is not unique on each row.

EDIT:

If you want the average of the submissions columns for these groups, use case:

SELECT ft.fruit,  
       AVG(CASE WHEN ftl.fruit_attribute IS NOT NULL THEN ft.submissions END) as avg_shared, 
       AVG(CASE WHEN ftl.fruit_attribute IS NULL THEN ft.submissions END) as avg_notshared, 
       COUNT(ftl.fruit_attribute) As attributes_shared_lemon,
       SUM(ftl.fruit_attribute IS NULL) As attributes_not_shared_lemon
FROM fruits ft LEFT JOIN
     fruits ftl
     ON ft.fruit_attribute = ftl.fruit_attribute and ftl.fruit = 'Lemon'
GROUP BY ft.fruit;

edited Oct 15 '15 at 21:40

answered Oct 15 '15 at 19:22

Gordon Linoff

1,242,037
58
646
786

This is awesome. Not sure why some of the others didn't understand the question. Nice query, Sir. – lcm Oct 15 '15 at 19:38
1

@lcm others still can't understand why you marked this answer as correct one, at the same time you wrote in comments **add two more columns for the average submissions for each of the "attributes_share_lemon" and "attribites_not_shared_lemon"** where it is clearly need **the average submissions** but Gordon did remove `submissions` from the query and won the race! :-) to me it seems really very funny :-D – Alex Oct 15 '15 at 19:45
1

@lcm Im glad you solve the problem. But for start you ask for `avg of submission`. And even when this is a very optimized answer, provide the exact result you already have. And doesn't have `submisions` or `average` – Juan Carlos Oropeza Oct 15 '15 at 19:46
@Alex . . . I agree, but I have now added that logic as well. – Gordon Linoff Oct 15 '15 at 21:41
1

@GordonLinoff there wasn't any question to you from the beginning:-) You did your guess and OP liked it. It is absolutely understandable. And now with your update that is really best answer. My comment was addressed to OP, that He should better formulate his question/issue/problem next time :-) – Alex Oct 16 '15 at 01:44

score 0 · Answer 2 · answered Oct 15 '15 at 19:33

It is still not clear what is your question or problem.

Just my guess. Probably you need to SUM(ft1.submissions)

and COUNT(DISTINCT ft2.fruit_attribute) to avoid counting same attribute multiple times.

and COUNT(DISTINCT ft1.fruit_attribute)-COUNT(DISTINCT ft2.fruit_attribute) to be sure that you have correct counter of currnt fruit attributes.

http://sqlfiddle.com/#!9/6ea5d8/22

SELECT ft1.fruit, SUM(ft1.submissions),   
    COUNT(DISTINCT ft2.fruit_attribute) As attributes_shared_lemon,
    COUNT(DISTINCT ft1.fruit_attribute)-COUNT(DISTINCT ft2.fruit_attribute) As attributes_not_shared_lemon
FROM fruits ft1 
LEFT JOIN fruits ft2
ON ft1.fruit_attribute = ft2.fruit_attribute
  AND ft2.fruit = 'Lemon'
GROUP BY ft1.fruit;

How to add averages w/ sum (case when...) in MySQL?

2 Answers2