Brewy Brewy - 1 year ago 68
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.)

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

In MySql (have to declare parameters - Average_1)

@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 (
Avg(Col_1) AS Average_1,
From Table
) as tmp
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download