CODEWITHSUNDEEP
sqlsql-servercountaggregation
M.R
M.R

Reputation: 59

table with sales and quantity of products - add column that count the products

i have this table: my table

i want to add column:count - that i can see for each product how many sales were, like this:

enter image description here

i try like this: 

update #t
set quantity = (select count(*) from #t group by product)

it is not good because it's return more then 1 value

Upvotes: 0

Views: 741

Answers (3)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use window function :

sum(quantity)  over (partition by product)

Or you can correlated your subquery : 

update t1
       set t1.quantity = (select sum(t.quantity) 
                          from #t as t  
                          where t.product = t1.product
                         )
from #t t1;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270401

Use window functions:

update #t
    set quantity = new_quantity
    from (select t.*, count(*) over (partition by product) as new_quantity
          from #t t
         ) t;

You should probably do this when you create the temporary table, but you can update the value afterwards as well.

Upvotes: 0

GMB
GMB

Reputation: 222582

Use window functions:

select
    t.*,
    sum(quantity) over(partition by product) cnt
from mytable t

As suggested by jarlh, a better idea than updating would be to create a view, so you don't need to worry about maintaining integrity of the derived column:

create view myview
select
    t.*,
    sum(quantity) over(partition by product) cnt
from mytable t

Upvotes: 0

Related Questions