Search code examples
sqlaggregatedimensions

Aggregation with Dimensions not working

I have a SQL table with several dimensions on it, when I aggregate it by day one dimensions, I get the right number, but when I add more dimensions I get a different number. This is super strange SK_DATE SK_COUNTRY Number1 Number2

So if I do

SELECT
    SK_DATE
    ,MAX(number1)
    ,MAX(number2)
FROM
    table 1
GROUP BY
    SK_DATE
;

No when I run the same thing. however, this time

SELECT
    SK_DATE
    ,SK_COUNTRY
    ,MAX(number1)
    ,MAX(number2)
FROM
    table 1
GROUP BY
    SK_DATE
    ,SK_COUNTRY
;

so on the first run I receive 5.2M for number 1 but when I run the second query I receive 6.4M

enter image description here

enter image description here

Thanks in advance

Solution

  • If you got more values of SK_COUNTRY in a single SK_Date, you should expect that the number of rows will bloat. Since Aggregation will be based on on unique combination of SK_DATE and SK_COUNTRY

    SELECT
SK_DATE
,SK_COUNTRY
,MAX(number1)
,MAX(number2)
FROM
table 1
GROUP BY
SK_DATE
,SK_COUNTRY
 ;