SQL Help

Question

SQL Help

Navigation

#1 by (2 votes)

2

I have the following problem: I have a XXX table that has the following fields: k803, codrer, period, goal, challenge .

Thetableshowsthegoalandchallengeofeach802storerepresentativeintheJanuary2018period.

Ineededtomakethemediaofthisstoreduringthisperiodandlooklikethis:

Thequestionisthis,Ineedtodothisforeverymonthandputallinformationinasinglequery,forexample"Goal Media and January Challenge", "Goal Media and and February Challenge" ... and so on.

Query of the mean:

SELECT  AVG(XXX.META)       AS META
    ,   AVG(XXX.DESAFIO)    AS DESAFIO 
FROM    XXX 
WHERE   XXX.K803    = '802' 
    AND XXX.periodo = '032018'

Does anyone have an idea if this can be done?

sql

asked by anonymous 04.01.2019 / 12:03

1 answer

Retrieve list written to file with Python Place menu at bottom edge of navbar Bootstrap 3

score 2 · Accepted Answer

I think something like this solves your problem:

SELECT      X.K803
        ,   X.CODREPR
        ,   X.PERIODO
        ,   X2.META
        ,   X2.DESAFIO
FROM        XXX X   
INNER JOIN  (
                SELECT      K803
                        ,   PERIODO
                        ,   AVG(META)       AS META
                        ,   AVG(DESAFIO)    AS DESAFIO
                FROM        XXX
                GROUP BY    K803
                        ,   PERIODO
            )   X2  ON  X2.K803     = X.K803
                    AND X2.PERIODO  = X.PERIODO

Then you can always apply the filters you understand.

If you do not need the CODREPR column then you can do it all at once!

SELECT      K803
        ,   PERIODO
        ,   AVG(META)       AS META
        ,   AVG(DESAFIO)    AS DESAFIO
FROM        XXX
GROUP BY    K803
        ,   PERIODO
WHERE       K803 = '802'

The WHERE clause is optional. If to include, it will only return the results to the '802' store, if remove will return to all stores.