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...

You are not summing in the previous month.

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``````
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.