0

I am trying to filter out all positive averages by using the line

 HAVING AVG(blurt_analysis.sentiment) < 0

however for an unexpected reason this line isn't doing as expected and in fact is making my query return nothing and im having a hard time figuring out why my query looks like

SELECT topic.id,topic.description,blurt.location,count(blurt.blurtid)as 'number of blurts',AVG(blurt_analysis.sentiment) as avgSentiment
FROM topic, blurt_analysis,blurt
WHERE topic.id=blurt_analysis.topicid AND blurt.blurtid = blurt_analysis.blurtid AND blurt.email = blurt_analysis.email 
group by blurt.location,topic.id
HAVING AVG(blurt_analysis.sentiment) < 0

the return looks like this without the having line and is empty with the line

1   shoes   California  2   2.5000
2   speaker California  3   1.3333
3   bats    California  3   1.0000
4   hoodies California  2   -0.5000
5   caps    California  1   -2.0000
6   pens    California  2   0.0000
7   games   California  4   1.2500
1   shoes   Colarado    1   1.0000
2   speaker Colarado    3   1.6667
3   bats    Colarado    1   1.0000
5   caps    Colarado    1   3.0000
7   games   Colarado    1   1.0000

with the first column being topicid the second being topic description the third being blurt location the 4th being the number of blurts and the 5th being avg sentiment(what im trying to filter positive numbers from)

UPDATE I still have not managed to figure out why the query doesnt work with having but I managed to filter positive numbers with another add making my sql look like

SELECT topic.id,topic.description,blurt.location,count(blurt.blurtid)as 'number of blurts',AVG(blurt_analysis.sentiment) as avgSentiment
FROM topic, blurt_analysis,blurt
WHERE topic.id=blurt_analysis.topicid AND blurt.blurtid = blurt_analysis.blurtid AND blurt.email = blurt_analysis.email AND blurt_analysis.sentiment <0
group by blurt.location,topic.id,topic.description
Alex Chapp
  • 137
  • 4
  • 16
  • Could you please rewrite your query using `JOIN` syntax? Unlikely that it will solve your problem, but we might better anderstand, what's going on in your query. – Paul Spiegel Nov 10 '16 at 01:01
  • I can try but I'm new to SQL and have not actually written a query with join before – Alex Chapp Nov 10 '16 at 01:10
  • And best would be if you create small sample data, that can reproduce your problem. – Paul Spiegel Nov 10 '16 at 01:15
  • Possible duplicate of [Is there ANY\_VALUE capability for mysql 5.6?](http://stackoverflow.com/questions/37089347/is-there-any-value-capability-for-mysql-5-6) – e4c5 Nov 12 '16 at 04:52

2 Answers2

0

Have you tried with NVL or CASE to replace null values? It worked for me one time.

i.e.: AVG(NVL(blurt_analysis.sentiment,0))

Vojtech Ruzicka
  • 16,384
  • 15
  • 63
  • 66
Jorge Navarro
  • 16
  • 1
0

You were lucky; now you're unlucky. each non-aggregate column in the select clause must appear in group by. A quick glance shows topic.description is among the missing.

Apparently MySQL tolerated your original query, but fell over when you added HAVING. Or you're getting nondeterministic results; I don't know. But I bet if you correct your query, your HAVING clause will behave better. :-)

James K. Lowden
  • 7,574
  • 1
  • 16
  • 31
  • I added that to the group by making it group by blurt.location,topic.id,topic.description and that did not change the behavior of my HAVING – Alex Chapp Nov 10 '16 at 00:48
  • `topic.description` is probably functionaly dependent on `topic.id`. Thus the result set is well defined. – Paul Spiegel Nov 10 '16 at 00:50
  • @PaulSpiegel meaning it doesn't need to be included after all? including it or leaving it off has had no change at all to the query or the broken HAVING line – Alex Chapp Nov 10 '16 at 00:52
  • 1
    @AlexChapp If `topic.id` is primary key, then adding `topic.description` to the group by clause should not change the result. However MySQL might complain depending on SQL mode. But it doesn't in your case. – Paul Spiegel Nov 10 '16 at 00:58
  • Thanks it is indeed primary – Alex Chapp Nov 10 '16 at 01:01