Geek Answers Handbook

Mysql - reusing computed values

I do not know exactly how to formulate this question, but here it is. I want to reuse the values ​​that I calculated in my query to calculate another value. Variables are the right word that I think. here is my request:

SELECT 
    t1.label as label,SUM(t1.totalEvents) as Entry,SUM(t2.totalEvents) as Back,  
    ROUND(Entry/Back*100,2) as 'Rate'
FROM 
    trackReports_daily t1
.... rest of query ...

Inside the round, I want to use the value returned by SUM (t1.totalEvents), but when I use Entry, I get this errorUnknown column 'Entry' in 'field list'

How else can I get the value there without recounting every time like this:

ROUND(SUM(t2.totalEvents)/SUM(t1.totalEvents)*100,2)
+5
source share
3 answers

You can use a subquery:

SELECT label, Entry, Back, ROUND(Entry/Back*100,2) as 'Rate'
FROM (
    SELECT SUM(t1.totalEvents) as Entry, SUM(t2.totalEvents) as Back, t1.label as label
    FROM trackReports_daily t1 
    .... rest of query ... 
) as temp;
+5
source

Take a look at this example.

select (select @t1:=sum(field1)),(select @t2:=sum(field2)),@t1/@t2 from table
+10
source

One option is to use a subquery in the FROM clause, for example:

SELECT 
    t1.label as label,SUM(t1.totalEvents) as Entry,t2.Back,  
    ROUND(t1.Entry/t2.Back*100,2) as 'Rate'
FROM 
    (SELECT *, SUM(totalEvents) as Entry FROM trackReports_daily) t1
    (SELECT *, SUM(totalEvents) as Back FROM someTable) t2
.... rest of query ...

If you want to reuse Rate in further requests, then it will be difficult, and I don’t know if it will have optimal performance, but I think it will do what you asked in your example.

+1
source

More articles:


All Articles