Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: Monday, April 18, 2016 9:57 PM
Points: 383, Visits: 2,633
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
Post #1584148
Posted Thursday, June 19, 2014 11:35 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 4:40 PM
Points: 40,390, Visits: 37,596
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."

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

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 23, 2016 3:57 PM
Points: 200, Visits: 208
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