SQL Group By comparison with Python Group By error

Question

I have written the following sql query:

SELECT
  *
FROM CSVDatabase.dbo.sales_info
  GROUP BY Company HAVING AVG(Sales)

Its based on the following dataset

However, I get the following error:

1 An expression of non-boolean type specified in a context where a condition is expected, near 'SET'.

I'm trying to get the mean across the whole dataset.

I have also written a python script that sets out to accomplish the same results as that in the sql script above, as follows:

df.groupBy('Company').mean().show()

The python script returns the results I would like to see from sql as shown below:

Can someone let me know where I'm going wrong with the sql script?

Answer 1

Seems to me like you are trying to use HAVING in a wrong way. The HAVING clause is used for:

HAVING filters records that work on summarized GROUP BY results. HAVING applies to summarized group records, whereas WHERE applies to individual records. Only the groups that meet the HAVING criteria will be returned. HAVING requires that a GROUP BY clause is present. WHERE and HAVING can be in the same query.

you have to move your AVG(Sales) in the SELECT clause or if you want to compere it you need to build statement in your HAVING clause. HAVING AVG(Sales)>yourValue comment down if you have more questions tnx :)

Answer 2

I think SQL should be like below

SELECT Company, AVG(Sales)
FROM CSVDatabase.dbo.sales_info
GROUP BY Company