2011-02-25

HeidiSQL

Very nice program to access remote SQL server.
Also, I`ve learned some neat things about MySql functions and extended use.
For example, here is some code
What does it do?
It`s looking for all similar months and returns an average value from all values on given month.
In the inner "select" I`m selecting all columns and I`m adding additional column, where the each month (meneshi) is added in each row using "where"
And then in the outer query I`m gruping values by newly created month column and calling an average function. It returns all average values for each group - month.
And lastly I`m ordering results by month. Easy as pie, actually.

Only thing I need to change is "ns" in third line from end of the script.
Next challenge would be to make average results for all ns.
select avg(lim), datums, meneshi
from
( select ns, lim, datums,
CASE
WHEN datums like '19__-01-__%' THEN '01'
WHEN datums like '19__-02-__%' THEN '02'
WHEN datums like '19__-03-__%' THEN '03'
WHEN datums like '19__-04-__%' THEN '04'
WHEN datums like '19__-05-__%' THEN '05'
WHEN datums like '19__-06-__%' THEN '06'
WHEN datums like '19__-07-__%' THEN '07'
WHEN datums like '19__-08-__%' THEN '08'
WHEN datums like '19__-09-__%' THEN '09'
WHEN datums like '19__-10-__%' THEN '10'
WHEN datums like '19__-11-__%' THEN '11'
WHEN datums like '19__-12-__%' THEN '12' END as meneshi
from hmetlimeni where ns=9664 ) as tble
group by meneshi
order by meneshi

Nav komentāru:

Ierakstīt komentāru