CODEWITHSUNDEEP
sql-serversubquery
DeadlyDagger
DeadlyDagger

Reputation: 609

SQL Server query for count sold item in certain dates

I want to write a query and use a subquery in it, that the result set will be the count of sold item in certain dates.

The table is like this:

item_name       sold          date
------------------------------------
A                 20           10.1
B                 10           10.3
A                 10           10.5
C                 20           10.4
A                 30           10.8

and the result set will be like this:

item_name       sold           date
-------------------------------------
A                 20            10.1
B                 10            10.3
A                 30            10.5
C                 20            10.4
A                 60            10.8

Upvotes: 1

Views: 355

Answers (2)

pratik garg
pratik garg

Reputation: 3342

you can go with following query

select t1.item_name,
      (select sum(sold) from table1 t2 where t2.date <= t1.date 
            and t2.item_name = t1.item_name ) as sold,
       t1.date
from table1 t1

isn't it so sipmle ... sql is very simple language ... just you have to think for your problem with calm and cool mind :)

Upvotes: 2

chopikadze
chopikadze

Reputation: 4239

select t1.item_name, sum(t1.sold) as sold, t2.date
from myTable t1
    inner join myTable t2 on (t1.item_name = t2.item_name) and (t1.date <= t2.date)
group by t1.item_name, t2.date

Upvotes: 2

Related Questions