Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

problem .... income during month in sql server 2008 Expand / Collapse
Author
Message
Posted Thursday, June 19, 2014 8:32 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 3:26 AM
Points: 2, Visits: 3
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
Post #1584138
Posted Thursday, June 19, 2014 10:34 PM This worked for the OP Answer marked as solution


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
SELECT DATEPART(MM,VISITINGDATE),SUM(COST) FROM yourtable
GROUP BY DATEPART(MM,VISITINGDATE)

Post #1584148
Posted Thursday, June 19, 2014 11:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:57 PM
Points: 35,778, Visits: 32,451
amr.moussa.87 (6/19/2014)
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


What do you want to display when the year changes over?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1584156
Posted Friday, June 20, 2014 12:24 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, December 19, 2014 3:12 AM
Points: 185, Visits: 177
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)
Post #1584164
Posted Friday, June 20, 2014 3:29 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 3:26 AM
Points: 2, Visits: 3
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)
Post #1584251
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse