June 19, 2014 at 8:32 pm
i have tried many thing
i have table called visiting, it have two column (visiting date) and ( cost ) like that
visiting_date cost
20-6-2014 50
20-6-2014 50
21-7-2014 200
21-7-2014 200
i want to make view that can sum cost of each month individual so output will be
month income
6 100
7 400
than you for helping
June 19, 2014 at 10:34 pm
SELECT DATEPART(MM,VISITINGDATE),SUM(COST) FROM yourtable
GROUP BY DATEPART(MM,VISITINGDATE)
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 19, 2014 at 11:35 pm
amr.moussa.87 (6/19/2014)
i have tried many thingi have table called visiting, it have two column (visiting date) and ( cost ) like that
visiting_date cost
20-6-2014 50
20-6-2014 50
21-7-2014 200
21-7-2014 200
i want to make view that can sum cost of each month individual so output will be
month income
6 100
7 400
than you for helping
What do you want to display when the year changes over?
--Jeff Moden
Change is inevitable... Change for the better is not.
June 20, 2014 at 12:24 am
I use to create unique MonthID based on Year and month combination.
select (datepart(yy,SalesDate)-2010)*12+datepart(MM,SalesDate) as MonthID, sum(total)
group by (datepart(yy,SalesDate)-2010)*12+datepart(MM,SalesDate)
-- Assuming 2010 as the minimum month
-Vijred (http://vijredblog.wordpress.com)
June 20, 2014 at 3:29 am
thank you guys your answer help me so much 🙂 i find the solution
SELECT SUM(cost) as income,
DATEPART(Month, visiting_date) as month,
DATEPART(Year, visiting_date) as year
FROM table1
GROUP BY DATEPART(Year, visiting_date), DATEPART(Month, visiting_date)
ORDER BY DATEPART(Year, visiting_date), DATEPART(Month, visiting_date)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply