Sum Function - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sum Function

Author

Topic

onetoof
Starting Member

19 Posts

Posted - 2006-03-02 : 13:51:01

Hi All,

Here is my script:

SELECT vou_co,SUM(vou_inv_amt) as "Total Invoice"
FROM voucher
WHERE vou_type = "I" and vou_inv_dt >'2006-01-31 00:00:00.000'
group by vou_co

Union all

SELECT vou_co, SUM(vou_inv_amt*-1) as "Total Credit"
FROM voucher
WHERE vou_type = "C" and vou_inv_dt >'2006-01-31 00:00:00.000'
group by vou_co

ORDER BY 1

Here are my results:

vou_co Total Invoice
------ ---------------------
10 624894.9800
20 120454.4800
20 -882.2000
30 12441.4900
40 66690.4800
50 10285.8300
60 23438.9500
60 -462.2200

(8 row(s) affected)

Here is my question:

How can I sum the values with the same co#?
For example:
co60 values are: 23438.95 and -462.22.
How can I sum those results?

Any help is appreciated and you would help to make me a star in my boss's eyes.

Cheers!!!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-02 : 13:55:28

[code]
SELECT vou_co, SUM(TotalInvoice)
FROM
(
SELECT vou_co,SUM(vou_inv_amt) AS TotalInvoice
FROM voucher
WHERE vou_type = "I" AND vou_inv_dt > '2006-01-31 00:00:00.000'
GROUP BY vou_co
UNION ALL
SELECT vou_co, SUM(vou_inv_amt*-1)
FROM voucher
WHERE vou_type = "C" AND vou_inv_dt > '2006-01-31 00:00:00.000'
GROUP BY vou_co
) t
GROUP BY vou_co
[/code]

Tara Kizer
aka tduggan

onetoof
Starting Member

19 Posts

Posted - 2006-03-02 : 13:57:53

Thanks so much TKIZER. You are awesome!!!

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-02 : 13:58:11

SELECT vou_co, SUM(case vou_type when 'C' Then -1 * vou_inv_amt else vou_inv_amt end) as "Total Invoice"
FROM voucher
WHERE vou_type in ('C', 'I') and vou_inv_dt >'2006-01-31 00:00:00.000'
group by vou_co

edit - too slow

onetoof
Starting Member

19 Posts

Posted - 2006-03-02 : 14:17:33

Hi again,

When I run both of the suggested scripts, my results drop the credit values (values associated with a "C" type).

Any suggestions?

Thanks again for the assistance!!

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-02 : 14:24:30

They aren't dropping the credits, they're totaling the invoice amounts.

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-03-02 : 14:25:54

Based on your sample data you should get

10 624894.9800
20 119572.28
30 12441.4900
40 66690.4800
50 10285.8300
60 22976.73

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-02 : 14:29:26

Both types are still in the result set. Do you only want the invoices summed? If not, what happens if you have 60 as the type of Invoice and Credit. Do you want them summed or only if they are the same type? Mine assumes you want them summed regardless of the type.

Tara Kizer
aka tduggan

syoselevsky
Starting Member

7 Posts

Posted - 2006-03-02 : 14:30:02

Hi There...

select vou_co,sum(vou_inv_amt) as "Sum"
from voucher
where vou_type in ('I','C') and vou_inv_dt > '2006-01-31 00:00:00.000'
group by vou_co

that should do it...

S

onetoof
Starting Member

19 Posts

Posted - 2006-03-02 : 15:55:39

OK ...here is why my numbers are off. This script currenty pulls from a table which has no cloumn refernce to a GL period. This is why I used the date that you see referenced. To get correct numbers I would need to use the GL period which is located in a different table named "voudet" and a column name of "voud_glpd_no".

In this gl column data looks like this: 200606

Sugestions?

Again, I do appreciate everyone's help.
Thanks.

onetoof
Starting Member

19 Posts

Posted - 2006-03-02 : 16:00:30

Each table has a similar column:
vou_co = voud_vou_co

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-02 : 16:04:32

SELECT ...
FROM voucher
INNER JOIN voudet
ON voudet.vou_co = voucher.voud_vou_co (or whichever column belongs to each)
WHERE voucher.voud_glpd_no = '200606'

Tara Kizer
aka tduggan

onetoof
Starting Member

19 Posts

Posted - 2006-03-02 : 16:20:08

I'm sorry TKIZER, I am a total newbie when it comes to SQL. My skills are more in the C++ arena. Can you please be so kind as to spell it out for me ? When I attempt to insert the "inner join" script, I get an infinte loop.

Thanks,
D.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-02 : 16:23:33

Post what you have. Also post the CREATE TABLE statement for both tables.

Tara Kizer
aka tduggan

onetoof
Starting Member

19 Posts

Posted - 2006-03-02 : 16:36:20

Please forgive me again..not sure if I have access to the create tables. The database that I am accessing is being created by an accounting software suite called "softrax".
You wrote:

SELECT vou_co, SUM(TotalInvoice)
FROM
(
SELECT vou_co,SUM(vou_inv_amt) AS TotalInvoice
FROM voucher
WHERE vou_type = "I" AND vou_inv_dt > '2006-01-31 00:00:00.000'
GROUP BY vou_co
UNION ALL
SELECT vou_co, SUM(vou_inv_amt*-1)
FROM voucher
WHERE vou_type = "C" AND vou_inv_dt > '2006-01-31 00:00:00.000'
GROUP BY vou_co
) t
GROUP BY vou_co

....and this works, but I need to pull data based on a gl period and not based on a date.

so I'm just not sure where this:

INNER JOIN voudet
ON voudet.vou_co = voucher.voud_vou_co (or whichever column belongs to each)
WHERE voucher.voud_glpd_no = '200606'

fits into the above.

Thanks.
D.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-02 : 16:39:45

It's hard for me to know where to put it. What do you intend to do with 200606? Is that instead of vou_inv_dt > '2006-01-31 00:00:00.000'?

I'm unclear as to what this post means:

quote:

OK ...here is why my numbers are off. This script currenty pulls from a table which has no cloumn refernce to a GL period. This is why I used the date that you see referenced. To get correct numbers I would need to use the GL period which is located in a different table named "voudet" and a column name of "voud_glpd_no".

In this gl column data looks like this: 200606

Perhaps this is what you want:


SELECT vou_co, SUM(TotalInvoice)
FROM
(
	SELECT vc.vou_co, SUM(vc.vou_inv_amt) AS TotalInvoice
	FROM voucher vc
	INNER JOIN voudet vd
	ON vd.vou_co = vc.voud_vou_co
	WHERE vd.voud_glpd_no = '200606' AND vc. vou_type = 'I'
	GROUP BY vc.vou_co
	UNION ALL
	SELECT vc.vou_co, SUM(vc.vou_inv_amt*-1)
	FROM voucher vc
	INNER JOIN voudet vd
	ON vd.vou_co = vc.voud_vou_co
	WHERE vd.voud_glpd_no = '200606'  AND vc.vou_type = 'C'
	GROUP BY vc.vou_co
) t
GROUP BY vou_co

Tara Kizer
aka tduggan

onetoof
Starting Member

19 Posts

Posted - 2006-03-02 : 16:43:59

yes, you are correct. I would then drop the "vou_inv_dt" all together

voud_glpd_no = 200606

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-02 : 16:45:55

I edited my post. See if the code that I added works.

Tara Kizer
aka tduggan

onetoof
Starting Member

19 Posts

Posted - 2006-03-02 : 17:06:40

The script puts me in an infinite loop.

Here is a script which returns the correct numbers based on a GL period:

declare @glperiod int
declare @company int
set @glperiod = 200606

select voucher.vou_no,voucher.vou_co,vou_type,
'Voucher Amount' =
case vou_type
when 'I' then voudet.voud_total_cost
when 'C' then CAST(voudet.voud_total_cost * -1 AS decimal(15,4))
end,
voucher.vou_ven_cd, voucher.vou_inv_no,voucher.vou_batch_no, voudet.voud_glpd_no, voud_glt_cd
from voucher right join voudet on (
voucher.vou_no = voudet.voud_vou_no and
voucher.vou_co = voudet.voud_vou_co)
where voudet.voud_glpd_no = @glperiod and voudet.voud_multi = 'N'
order by voucher.vou_co,voucher.vou_batch_no, voucher.vou_no

.................however it does not sum the values in this format:
In this case, I would need to sum the "Voucher Amount" column

10 624894.9800
20 119572.28
30 12441.4900
40 66690.4800
50 10285.8300
60 22976.73

onetoof
Starting Member

19 Posts

Posted - 2006-03-02 : 17:26:59

Suggestions Anyone :o)

Thanks,
D.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-03-03 : 12:36:22

quote:

The script puts me in an infinite loop.

I don't know what you mean by an infinite loop as that's not possible with the code that I posted. It may be slow due to lack of indexes or your database design, but it will eventually return a record set.

Tara Kizer
aka tduggan

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources