Sql Query with Total

Hi guys,

I can't do this in sql query.

Code:

---

Site        Capital            TotalCapital
Site A        1,995,000        2,229,500
Site A        234,500       
Site B        4,320,234        4,320,234
Site C        2,381,354        2,504,766
Site C        123,412

---

The query will sum up if the site is the same. Is this possible in sql query? Or do I really need crystal report to accomplish this?

Code:

---

Site        Capital            TotalCapital
Site A        1,995,000       
Site A        234,500       
                      2,229,500
Site B        4,320,234       
                      4,320,234
Site C        2,381,354       
Site C        123,412
                      2,504,766

---

And also to use the totalcapital result in a formula to add taxes. Thanks in advance.

You can't get the format you want from an SQL statement, you would need some client side code (your own, or what is built into something like Crystal).

You could however get data like this easily:

Code:

---

Site        TotalCapital
Site A        2,229,500
Site B        4,320,234
Site C        2,504,766

---

To do that you simply need a Group By, eg:

Code:

---

SELECT Site, Sum(Capital) as TotalCapital
FROM tablename
GROUP BY Site

---

I'll try to use Crystal Report, thanks.