•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 397,817 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 2,620 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser:
Views: 1261 | Replies: 2
 |
•
•
Join Date: May 2007
Posts: 1
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
Query Total Asset Asset Table
+-------+---------+-----------+
| Date | Asset | Value |
+-------+---------+-----------+
|1/1/07 |Building | $1000 |
|1/2/01 |Computer | $ 500 |
|2/1/07 | Fax | $ 100 |
+-------+--------+------------+
Result Table I would Like;
+-------+---------------+
| Month | Total Asset |
+-------+---------------+
| 1 | $1500 |
| 2 | $1600 |
+-------+---------------+
However using the following query like this:
"SELECT Sum(Value) AS Total_Aset, Month(Date) AS Month
FROM Asset
WHERE Asset.date between Begindate And Enddate
GROUP BY Month(date);"
I have incorrect query like
+-------+---------------+
| Month | Total Asset |
+-------+---------------+
| 1 | $1500 |
| 2 | $100 |
+-------+---------------+
Can Anyaone help me on this problem?
Thanx...
+-------+---------+-----------+
| Date | Asset | Value |
+-------+---------+-----------+
|1/1/07 |Building | $1000 |
|1/2/01 |Computer | $ 500 |
|2/1/07 | Fax | $ 100 |
+-------+--------+------------+
Result Table I would Like;
+-------+---------------+
| Month | Total Asset |
+-------+---------------+
| 1 | $1500 |
| 2 | $1600 |
+-------+---------------+
However using the following query like this:
"SELECT Sum(Value) AS Total_Aset, Month(Date) AS Month
FROM Asset
WHERE Asset.date between Begindate And Enddate
GROUP BY Month(date);"
I have incorrect query like
+-------+---------------+
| Month | Total Asset |
+-------+---------------+
| 1 | $1500 |
| 2 | $100 |
+-------+---------------+
Can Anyaone help me on this problem?
Thanx...
•
•
Join Date: Jan 2007
Posts: 2,524
Reputation:
Rep Power: 7
Solved Threads: 105
You are not summing in the previous month.
Daylight-saving time uses more gasoline
•
•
Join Date: Mar 2007
Posts: 28
Reputation:
Rep Power: 0
Solved Threads: 0
Light Poster
•
•
•
•
Originally Posted by MidiMagic 
You are not summing in the previous month.
As Midimagic said, you need to sum the previous month(s) too. One way to do this is using a join
SELECT curr.AssetYear, curr.AssetMonth,
SUM(curr.Total + ISNULL(prev.Total, 0)) AS Asset_Total
FROM (
SELECT Year(Date) AS AssetYear,
Month(Date) AS AssetMonth,
Sum(Value) AS Total
FROM Asset
WHERE date between @beginDate And @endDate
GROUP BY Year(Date), Month(Date)
)
AS curr LEFT JOIN
(
SELECT Year(Date) AS AssetYear,
Month(Date) AS AssetMonth,
Sum(Value) AS Total
FROM Asset
WHERE date between @beginDate And @endDate
GROUP BY Year(Date), Month(Date)
)
AS prev ON curr.AssetYear = prev.AssetYear
AND curr.AssetMonth > prev.AssetMonth
GROUP BY curr.AssetYear, curr.AssetMonth |
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
Linear Mode
- SQL SERVER 2000 Selecting a record based on an aggregate function (MS SQL)
- report (Visual Basic 4 / 5 / 6)
- find and query an LDAP path with PHP? (PHP)
- ASP- SQL Server..Query help.... (ASP)
- help! need to accumulate total (ColdFusion)
- Need help with getting a total for individuals (ColdFusion)
- member group query (MySQL)
Other Threads in the MS SQL Forum
- Previous Thread: delete data, then shift its number....
- Next Thread: update two rows in one query
