Sum of column fields in SQL server

Question

1.00/5 (3 votes)

See more:

SQL-Server-2005

Hi,

I have a table as below:-

CSS

Date                    Qty Unit Rate   Amount 
2013-04-02 00:00:00.000 1   PCS 200.00  200.00
2013-04-02 00:00:00.000 2   PCS 230.00  460.00
2013-04-02 00:00:00.000 3   PCS 200.00  600.00
2013-04-02 00:00:00.000 320 PCS 178.00  56960.00
2013-04-02 00:00:00.000 360 PCS 118.00  42480.00
2013-04-03 00:00:00.000 360 PCS 142.00  51120.00
2013-04-03 00:00:00.000 360 PCS 178.00  64080.00
2013-04-03 00:00:00.000 480 PCS 116.00  55680.00
2013-04-04 00:00:00.000 480 PCS 134.00  64320.00
2013-04-04 00:00:00.000 520 PCS 132.00  68640.00

I want the SUMMARY of the above table:
2013-04-02 00:00:00.000 1046PCS 185.20 49136.00
2013-04-03 00:00:00.000 1100PCS 145.33 113208.00
---
Please help me with query...

Posted 8-Jul-13 20:50pm

rupai99

Add a Solution

Comments

rupai99 9-Jul-13 3:03am

Hi,

The above data was generated by the below query...

SELECT dbo.SalePurchase.tran_date, Convert(int,SUM(dbo.IT.iQty))as Qty,dbo.Item.UOM, dbo.IT.iRate, dbo.IT.iAmount
FROM dbo.SalePurchase LEFT OUTER JOIN
dbo.IT ON dbo.SalePurchase.Code = dbo.IT.SPCode LEFT OUTER JOIN
dbo.Item ON dbo.IT.ItemCode = dbo.Item.Code where dbo.SalePurchase.tran_date BETWEEN '20130401' AND
'20130709' AND dbo.SalePurchase.Tran_type='CS'
Group By dbo.SalePurchase.tran_date,dbo.IT.iQTY,dbo.Item.UOM, dbo.IT.iRate, dbo.IT.iAmount

For some reason sum(qty) didnot work...

Please help..

Prasad Khandekar 9-Jul-13 3:13am

You can try SQL similar to one shown below.
SELECT Date, SUM(Qty) AS Quantity, SUM(Amount)/SUM(Qty) AS UnitRate, SUM(Amount)
FROM your_table
GROUP BY Date
ORDER BY Date

Regards,

Raja Sekhar S 9-Jul-13 3:51am

Rupai99...
Don't Post same Question again and again...
http://www.codeproject.com/Questions/617623/Referencing-a-data-field-in-two-columns-based-on-c

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

**Zoltán Zörgő** · Answer 1 · 2013-07-08T20:54:00

Solution 1

This is quite simple:

SQL

select date, sum(qty), unit, avg(rate), sum(amount)
from yourtable
group by date, unit

Posted 8-Jul-13 20:54pm

Zoltán Zörgő