CODEWITHSUNDEEP
sqlgroup-byduplicatesaverageaggregate-functions
Stankevix
Stankevix

Reputation: 45

Duplicate rows after AVG function in SQL

I'm currently having issues creating two columns with AVG grouped by 2 ID columns

I've tried the below code to try and resolve this.

My results, I get replicate the same ID_STORE countless time with AVG for each row. My goal is to generate a single line for ID_STORE (DISTINCT) and Multiple lineS for ID_BRICK by Aggregating the two AVGs.

| ID_BRICK | ID_STORE | AVG1  | AVG2  |
| 00000001 | 0003     | 70.47 | 85.40 |
| 00000001 | 0003     | 61.55 | 85.40 |
| 00000001 | 0003     | 55.33 | 85.40 |

...

| ID_BRICK | ID_STORE | AVG1   | AVG2  |
| 00000001 | 0004     | 80.47  | 85.40 |
| 00000001 | 0004     | 161.55 | 85.40 |
| 00000001 | 0004     | 15.33  | 85.40 |

...

| ID_BRICK | ID_STORE | AVG1   | AVG2  |
| 00000001 | 0005     | 60.47  | 85.40 |
| 00000001 | 0005     | 11.85  | 85.40 |
| 00000001 | 0005     | 19.23  | 85.40 |

My query

SELECT 
    ID_STORE,
    ID_BRICK,
    AVG1,
    AVG2,
CASE
    WHEN AVG1>=AVG2 THEN 'HIGH TOP3'
    WHEN AVG1<AVG2  THEN 'LOW TOP3'
END AS CLASS_TOP
FROM(
    SELECT
        ID_STORE,
        ID_BRICK,
        AVG(V.PRICE) AS AVG1,
        AVG(V.PRICE) OVER(PARTITION BY L.ID_BRICK) AS AVG2
    FROM CPVBI.dbo.SALES V
        INNER JOIN CPVBI.dbo.STORE L
            ON (L.ID_STORE=V.ID_STORE)
        INNER JOIN CPVBI.dbo.BRAND M
            ON(V.ID_BRAND = M.ID_BRAND)
    WHERE (L.FLAG_STORE = '1' AND M.FLAG_BRAND = 'S') AND ( ID_BRAND = '53112' OR ID_BRAND = '53130' OR ID_BRAND = '53111')
    GROUP BY L.ID_BRICK, L.ID_STORE, V.PRICE
)AS T2
ORDER BY ID_STORE;

I would like the results of my query to produce

| ID_BRICK | ID_STORE | AVG1  | AVG2  |
| 00000001 | 0003     | 70.47 | 85.40 |
| 00000001 | 0004     | 65.33 | 85.40 |
| 00000001 | 0005     | 67.33 | 85.40 |

Upvotes: 1

Views: 612

Answers (1)

avery_larry
avery_larry

Reputation: 2135

UNTESTED You should be able to get the average per brick in a subquery and then join that to the average per store:

SELECT L1.ID_STORE, L1.ID_BRICK, AVG1, AVG2,
    CASE WHEN AVG1 >= AVG2 THEN 'HIGH TOP3' ELSE 'LOW TOP3' END AS CLASS_TOP
FROM (
    SELECT
        ID_STORE,
        ID_BRICK,
        AVG(V.PRICE) AS AVG1
    FROM CPVBI.dbo.SALES V
    INNER JOIN CPVBI.dbo.STORE L ON L.ID_STORE = V.ID_STORE
    INNER JOIN CPVBI.dbo.BRAND M ON V.ID_BRAND = M.ID_BRAND
    WHERE L.FLAG_STORE = '1'
        AND M.FLAG_BRAND = 'S'
        AND ID_BRAND IN ('53112', '53130', '53111')
    GROUP BY L.ID_BRICK, L.ID_STORE
) L1
INNER JOIN (
    SELECT
        ID_BRICK,
        AVG(V.PRICE) AS AVG2
    FROM CPVBI.dbo.SALES V
    INNER JOIN CPVBI.dbo.STORE L ON (L.ID_STORE=V.ID_STORE)
    INNER JOIN CPVBI.dbo.BRAND M ON(V.ID_BRAND = M.ID_BRAND)
    WHERE L.FLAG_STORE = '1'
        AND M.FLAG_BRAND = 'S'
        AND ID_BRAND IN ('53112', '53130', '53111')
    GROUP BY L.ID_BRICK
) L2 on L1.ID_BRICK = L2.ID_BRICK
ORDER BY L1.ID_STORE;

Upvotes: 1

Related Questions