CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
Nicholas Porter
Nicholas Porter

Reputation: 2971

SQL total for each row combination

Are there any types of aggregate functions that could help calculate the following?

If I have the following data, I want the sum total for each duplicated row.

   date    |  country     | color | measure |  left | position 
---------------------------------------------------------------
2020-02-01  united states  red        33       5.3      none  
2020-02-01  united states  red        33       5.3      none  
2020-02-01  united states  red        33       5.3      none  
2020-02-03  vietnam        green      44       9.2      none  
2020-02-01  united states  red        33       5.3      none  
2020-02-01  united states  red        33       5.3      none  
2020-02-03  vietnam        green      44       9.2      none  
2020-02-01  canada         yellow     20       3.1      none  
2020-02-06  mexico         orange     2        9.0      miss
2020-02-06  mexico         red        1        3.0      none

The output would provide total number each row occurs:

   date    |  country     | color | measure |  left | position | total
-----------------------------------------------------------------------
2020-02-01  united states  red        33       5.3      none      5
2020-02-03  vietnam        green      44       9.2      none      2
2020-02-01  canada         yellow     20       3.1      none      1
2020-02-06  mexico         orange     2        9.0      miss      1
2020-02-06  mexico         red        1        3.0      none      1

Upvotes: 0

Views: 29

Answers (1)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186833

Why not good old group by and count?

  select date,
         country,
         color,
         measure,
         left,
         position,
         count(1) as total      
    from MyTable
group by date,
         country,
         color,
         measure,
         left,
         position

Upvotes: 2

Related Questions