We help IT Professionals succeed at work.

SUM(YEAR),SUM(MONTH)---sql

HI every one

i have one table name call trans_action
item_id,   income   ,date

i need one query which gives me total amount for month and year particular Item_id as
item_id   income      date
100         $50         01/01/2010
100         $150       01/02/2010
200         $50         01/02/2010
300         $50         01/03/2010
200         $50         01/03/2010

result for 02/2010
item_id      MonthAmount         yearAmount
100              150                          250
200                50                          100

thanks in Advance
Comment
Watch Question

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
This should do:

SELECT item_id, sum(income) over ( partition by item_id, year(date), month(date)) monthamount,
sum(income) over ( partition by item_id, year(date)) yearlyamount
FROM trans_action
GROUP BY item_id

Commented:
SELECT item_id, MONTH(date), SUM(income)  AS MonthAmount
FROM trans_action
GROUP BY item_id, MONTH(date)

SELECT item_id, YEAR(date), SUM(income)  AS MonthAmount
FROM trans_action
GROUP BY item_id, YEAR(date)
Rajkumar GsSoftware Engineer

Commented:
Here is another way. Specify the month & year in the inside queries.

Raj
select a.item_id, a.income as MonthAmount, b.income As yearAmount
from
(
	select item_id, month(date) as month, year(date) as year, sum(income) as income from #table
	where month(date) = 2 and year(date) = 2010
	group by item_id, month(date), year(date)
) a inner join
(
	select item_id, year(date) as year, sum(income) as income from #table
	where month(date) <= 2 and year(date) = 2010
	group by item_id, year(date)
) b
on a.item_id = b.item_id and a.year = b.year

Open in new window

Expert of the Quarter 2010
Expert of the Year 2010
Commented:
This form allows you to specify the month that you are interested in, as you have stated the sample for 2010/02

select t.item_id,
      MonthAmount=SUM(t.income),
      yearAmount=(
            select SUM(t2.income)
            from trans_action t2
            where t2.item_id=t.item_id
              and t2.[date] >= left(CONVERT(char(7), t.[date], 102),4)+'0101'
              and t2.[date] <= left(CONVERT(char(7), t.[date], 102),4)+'1231')
from trans_action t
where t.[date] >= '20100201' and t.[date] < '20100301'
group by t.item_id, CONVERT(char(7), t.[date], 102)

The form  "t.[date] >= '20100201' and t.[date] < '20100301'"  allows the index on trans_action.date to be used.
The convert(..t.[date]) is required to bring the date context into the yearly amount subquery
Naveen KumarProduction Manager / Application Support Manager

Commented:
if you want it for the given month/year, then additionally include the where clause for that as well.

You can try :

SELECT item_id, sum(income) over ( partition by item_id, year(date), month(date)) monthamount,
sum(income) over ( partition by item_id, year(date)) yearlyamount
FROM trans_action
where year(date) = '2008' --> change accordingly here....
GROUP BY item_id

Commented:
Hi,

Are you sure the result you are expecting is correct?? because I think result should give YearlyAmount 2oo for ItemID 100....

Here is a query to get the result...

SELECT      item_id, SUM (Income) AS MonthAmount,
      ( SELECT      SUM(Income)
        FROM      trans_action ta1
        WHERE      ta1.item_id = ta.item_id ) AS YearAmount
FROM      trans_action ta
WHERE      MONTH(Date) = 2
GROUP BY item_id
Top Expert 2011

Commented:
select item_id
       ,sum(case when month(date) = 2 then income else 0 end) as MonthAmt
       ,sum(income) as YearlyAmt
from trans_action
where Year(date) = 2010
group by item_id
order by 1
Top Expert 2011

Commented:
select item_id
       ,sum(case when month(date) = 2 then income else 0 end) as MonthAmt
       ,sum(income) as YearlyAmt
from trans_action
where Year(date) = 2010
group by item_id
order by 1
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018
Commented:
To be honest there are a LOT of ways to achieve the results. The "best" will depend on a few factors such as indexes, number of rows etc....

Expecting that itemid is quite possibly a key to yet another table (maybe products or something similar) otherwise it would be worthwhile grouping on, there could even be other possibilities.

Also depends on if this is a straight query, or, something that might be part of a procedure - because depending on how you are passing those parameters, there are better (faster) ways to use date functions that keep it sargable if dates are used in an index.

So, just to add yet another different way of doing it... the humble inline query can be very effective...

SELECT ta1.item_id
     , sum(ta1.income) as month_income
     , (select sum(income) from trans_action ta2 where ta1.item_id = ta2.item_id and year(ta1.date) = year(ta2.date)) as year_income
FROM trans_action ta1
WHERE month(ta1.date) = 02 and year(ta1.date) = 2010  
GROUP BY item_id,year(ta1.date)

-- best to have date between '20100201' and '20100228 23:59:59'    and there are ways to calc those params

Author

Commented:
thank you very much every one.i will try each of these solutions and let you  know thank you.
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
The only sargeable query so far is still http:#a32968220

http://en.wikipedia.org/wiki/Sargable
summary: index can be used

It is probably unimportant if your table is tiny or small, but if you are dealing with millions of records, the performance (or lack of) will start to show.
Rajkumar GsSoftware Engineer

Commented:
Hi shasanji,

Did you try and what is the update ?

Raj
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Interesting.

There are only 2 queries that return the desired result.

cyberkiwis (as already highlighted) and mine return the same results (mine is a bit inefficient), and most interesting is RajkumarGS http:#32968012 actually calculates the Year figure up to and including the month for that year (e.g. run for 02/2010 in March and the year figure is up to February)

alas lowfatspread returns all months for the year, not just the required 02/2010
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Recomendation to Split: cyberkiwi (http:#32968220), and mark_wills (http:#32971689)

Proof :

if object_id('tempdb..#trans_action','u') is not null drop table #trans_action

create table #trans_action (item_id int, date datetime, income money)

insert #trans_action values (100 ,'2010-01-01', 50.00 )
insert #trans_action values (100 ,'2010-02-01', 150.00 )
insert #trans_action values (200 ,'2010-02-01', 50.00 )
insert #trans_action values (300 ,'2010-03-01', 50.00 )
insert #trans_action values (200 ,'2010-03-01', 50.00 )

------------------------------------------------------------------------------------------------------------------
/* Target results from question header for 02/2010 

100	150.00	250.00    -- except this should probably be 200 because there is not enough activity for $250.00 worth.
200	50.00	100.00    -- note that this includes the full year values, not restricted to YTD

the above results show that item 300 is missing - due to date format of d/m/y as evidenced by the monthly figure for item_id 100 and 200

*/
------------------------------------------------------------------------------------------------------------------
-- mark_wills

SELECT ta1.item_id
     , sum(ta1.income) as month_income
     , (select sum(income) from #trans_action ta2 where ta1.item_id = ta2.item_id and year(ta1.date) = year(ta2.date)) as year_income
FROM #trans_action ta1
WHERE month(ta1.date) = 02 and year(ta1.date) = 2010   
GROUP BY item_id,year(ta1.date)

-- NB, using the "best to have date" recommendation, the same query can be more efficiently written (with no change in results) as:

SELECT ta1.item_id
     , sum(ta1.income) as month_income
     , (select sum(income) from #trans_action ta2 where ta1.item_id = ta2.item_id and ta2.date between '20100101' and '20101231 23:59:59') as year_income
FROM #trans_action ta1
WHERE ta1.date between '20100201' and '20100228 23:59:59'  
GROUP BY item_id

/* mark_wills results complies

100	150.00	200.00
200	50.00	100.00

*/
------------------------------------------------------------------------------------------------------------------
-- lowfatspread

select item_id
       ,sum(case when month(date) = 2 then income else 0 end) as MonthAmt
       ,sum(income) as YearlyAmt
from #trans_action
where Year(date) = 2010
group by item_id
order by 1 

/* lowfatspread results does not comply

100	150.00	200.00
200	50.00	100.00
300	0.00	50.00        -- should be suppressed.

*/
------------------------------------------------------------------------------------------------------------------
-- cyberkiwi

select t.item_id,
      MonthAmount=SUM(t.income),
      yearAmount=(
            select SUM(t2.income)
            from #trans_action t2
            where t2.item_id=t.item_id
              and t2.[date] >= left(CONVERT(char(7), t.[date], 102),4)+'0101'
              and t2.[date] <= left(CONVERT(char(7), t.[date], 102),4)+'1231')
from #trans_action t
where t.[date] >= '20100201' and t.[date] < '20100301'
group by t.item_id, CONVERT(char(7), t.[date], 102)

/* cyberkiwi results comply

100	150.00	200.00
200	50.00	100.00

*/
------------------------------------------------------------------------------------------------------------------
-- RajkumarGS

select a.item_id, a.income as MonthAmount, b.income As yearAmount
from
(
	select item_id, month(date) as month, year(date) as year, sum(income) as income from #trans_action
	where month(date) = 2 and year(date) = 2010
	group by item_id, month(date), year(date)
) a inner join
(
	select item_id, year(date) as year, sum(income) as income from #trans_action
	where month(date) <= 2 and year(date) = 2010
	group by item_id, year(date)
) b
on a.item_id = b.item_id and a.year = b.year

/* RajkumarGS results do not comply

100	150.00	200.00
200	50.00	50.00        -- year should be 100.00

*/

Open in new window