Assistance with SQL

Question

Guys I have the following problem... I have a table XXX that has the following fields: k803, codrer, periodo, meta, challenge.

The table shows the goal and challenge of each store representative 802 in the period of January 2018.

I had to make the media of this store in this period staying like this:

The point is, I need to do this for every month and put all the information in one query just, for example "January goal and challenge media," "goal media and February challenge," ... and so on.

Query average:

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

Does anyone have any idea if this can be done ?

Answer 1

I think something like that 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 want.

---

If you don’t need the column CODREPR 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 clause WHERE is optional. If you include it, it will only return the results to the '802' store, if removed it will return to all stores.