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.