Brewy Brewy - 9 months ago 30
SQL Question

SQL Group by Including Parameters / Calculations (converting from Access to MySql)

I'm sure this is easy for some, but I'm converting from Access to MySql and finding little quirks and going nuts. Been looking for hours it feels like but not finding a clear solution... Hoping for some guidance. Couple steps that I have worked through but stuck.

In Access you could use the parameters for future/continued SQL statements (example Average_1 can be multiplied etc.)

Select
Avg(Col_1) AS Average_1,
Average_1 * 2 AS Twice_Average_1
From Table


In MySql (have to declare parameters - Average_1)

Select
@Average_1 := Select(Avg(Col_1) From Table)
@Average_1 * 2 AS Twice_Average_1


No Problems above - get Average_1, Twice_Average_1
Except I want to do it via Group By

In Access it was easy, if I wanted to group average results by another column just added

Select Col_Group,...
....
Group By Col_Group


The above would yield great groups of averages... Sql isn't playing as nice I am stuck trying to pass the params per row.

Need the results:

Group_A:Average_1, Group_A:Twice_Average_1
Group_B:Average_1, Group_A:Twice_Average_1
Group_C:Average_1, Group_A:Twice_Average_1

Answer Source

You can always go with:

Select Col_Group, Average_1, Average_1*2 as Twice_Average_1
from (
Select 
Avg(Col_1) AS Average_1,
Col_Group 
From Table
) as tmp