Why does adding a SUM(column) throw a group by error [SQL]-CodePudding

I found some similar questions, but none of the solutions would work, nor did they explain what was causing the issue.

I have a working query

SELECT pages.pageString pageName, timeSpent
FROM
     (SELECT `page_id`, SUM(`time_spent`) as timeSpent
      FROM `pageViews` 
      WHERE `time_spent` > 0 
      GROUP BY `page_id`) myTable
JOIN pages ON pages.id = page_id 
ORDER BY timeSpent DESC 
LIMIT 5

This returns results that look like

 ------------------------------ ----------- 
| pageName                     | timeSpent |
 ------------------------------ ----------- 
| page 1                       |    394292 |
| page 2                       |     66990 |
| page 3                       |     53896 |
| page 4                       |     37796 |
| page 5                       |     14982 |
 ------------------------------ -----------

I'd like to add a row containing the percentage of timeSpent relative to the other pages, to start I added a SUM(timeSpent) to my query but that throws an error

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'pages.pageString'

Im not sure why this column is effected by adding this new column to the select statement.

Sadly any solution involving changing sql settings won't work due to company policy. I appreciate any advice

CodePudding user response：

You did not say where you tried to put the sum(timeSpent) but I believe one can try to reconstruct with the error message:

In aggregated query without GROUP BY, expression #1 of SELECT list contains nonaggregated column 'pages.pageString'

It says what the problem is. You added sum(timeSpent) to the projection, but the SQL statement does not have a GROUP BY, in particular it mentions the first item which should be aggregated pages.pageString.

It would mention the other ones too, once you fix this one.

On the other hand, please make sure you post exactly the failing SQL statement instead of trying to describe how to get the error you have. It's better for us who try to help.

CodePudding user response：

Adding a column containing the percentage of timeSpent relative to the sum of all pages

SELECT pages.pageString pageName, timeSpent,
  , timeSpent / sum(timeSpent) over() * 100 p
FROM
     (SELECT `page_id`, SUM(`time_spent`) as timeSpent
      FROM `pageViews` 
      WHERE `time_spent` > 0 
      GROUP BY `page_id`) myTable
JOIN pages ON pages.id = page_id 
ORDER BY timeSpent DESC 
LIMIT 5