Subtotal according to the InvoiceId

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 2005 Forums

Transact-SQL (2005)

Subtotal according to the InvoiceId

Author

Topic

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-11-23 : 01:09:35

Hi guys,

I have the data like this.
I want to sum the amountdue according to companyname alongwith InvoiceId.

declare @t1 table(Companyname varchar(4000),Invoiceid int,amountdue decimal(5),amountrcvd decimal(5),balance decimal(5))
insert into @t1(Companyname,Invoiceid,amountdue,amountrcvd,balance)
SELECT 'ABCD' 1 10000 10000 0 UNION ALL
SELECT 'ABCD' 2 20000 20000 0 UNION ALL
SELECT 'ABCD' 7 2500 350 2150 UNION ALL
SELECT 'Pqrs' 4 50500 50500 0 UNION ALL
SELECT 'Pqrs' 5 36000 36000 0 UNION ALL
SELECT 'Pqrs' 6 821 821 0 UNION ALL
SELECT 'Pqrs' 8 5000 5000 0 UNION ALL

I used the the query like this

select * from (SELECT Companyname,SUM(amountdue) AS amountdue ,row_number()
over(partition by Companyname order by Companyname desc)as rowid from @t1
group by Companyname)t1

It shows the output like this

ABCD 32500 1
Pqrs 92321 1

But I want to show the invoiceid also in the resultset.If i use the query like this

select * from (SELECT Companyname,invoiceid,SUM(amountdue) AS amountdue ,row_number()
over(partition by Companyname order by Companyname desc)as rowid from @t1
group by Companyname,invoiceid)t1

The output is

ABCD 1 10000 1
ABCD 2 20000 2
ABCD 7 2500 3
Pqrs 4 50500 1
Pqrs 5 36000 2
Pqrs 6 821 3
Pqrs 8 5000 4

Because the invoiceid has been used.

Is was wondering if is there any workaround for this or I will have to do it in the front end using datatable.

Regards

suresha_b
Yak Posting Veteran

82 Posts

Posted - 2007-11-23 : 02:19:25

select CompanyName, InvoiceId,
(select sum(AmountDue) from @t1 d where d.CompanyName = t.CompanyName),
row_number() over(partition by Companyname order by Companyname desc)as rowid
from @t1 t

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-11-23 : 02:35:32

Sorry Guys for the wrong sql statements in declare table
Here it is again
declare @t1 table(Companyname varchar(4000),Invoiceid int,amountdue decimal(5),amountrcvd decimal(5),balance decimal(5))
insert into @t1(Companyname,Invoiceid,amountdue,amountrcvd,balance)
SELECT 'ABCD', 1 ,10000 ,10000 ,0 UNION ALL
SELECT 'ABCD', 2, 20000, 20000, 0 UNION ALL
SELECT 'ABCD', 7, 2500, 350, 2150 UNION ALL
SELECT 'Pqrs', 4 ,50500 ,50500, 0 UNION ALL
SELECT 'Pqrs', 5, 36000 ,36000, 0 UNION ALL
SELECT 'Pqrs', 6 ,821, 821, 0 UNION ALL
SELECT 'Pqrs', 8, 5000, 5000, 0

And suresha thank you for the reply.But cant the output be something like we do using rollup statement.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-23 : 05:46:38

what is the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

ayamas
Aged Yak Warrior

552 Posts

Posted - 2007-11-23 : 08:46:55

quote:
Originally posted by khtan

what is the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

ABCD 1 10000 1
ABCD 2 20000 2
ABCD 7 2500 3
32500
Pqrs 4 50500 1
Pqrs 5 36000 2
Pqrs 6 821 3
Pqrs 8 5000 4
92321

Something when we use rollup.It shows the sum of data in the column but I want to do it according to the companyname.The sum of companyname ABCD is 32500 while that of pqrs is 92321.I want to do it according to company name.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-23 : 09:24:31

[code]DECLARE @t1 TABLE
(
Companyname varchar(4),
Invoiceid int,
amountdue decimal(5),
amountrcvd decimal(5),
balance decimal(5)
)
INSERT INTO @t1(Companyname,Invoiceid,amountdue,amountrcvd,balance)
SELECT 'ABCD', 1, 10000, 10000, 0 UNION ALL
SELECT 'ABCD', 2, 20000, 20000, 0 UNION ALL
SELECT 'ABCD', 7, 2500, 350, 2150 UNION ALL
SELECT 'Pqrs', 4, 50500, 50500, 0 UNION ALL
SELECT 'Pqrs', 5, 36000, 36000, 0 UNION ALL
SELECT 'Pqrs', 6, 821, 821, 0 UNION ALL
SELECT 'Pqrs', 8, 5000, 5000, 0

SELECT Companyname, Invoiceid, AountDue, rowno
FROM
(
SELECT Companyname,
Invoiceid,
AountDue = SUM(amountdue),
rowno = row_number() OVER (PARTITION BY Companyname ORDER BY Companyname)
FROM @t1
GROUP BY Companyname, Invoiceid WITH rollup
) a
WHERE Companyname IS NOT NULL
ORDER BY Companyname, rowno

/*
Companyname Invoiceid AountDue rowno
----------- ----------- -------- -----
ABCD 1 10000 1
ABCD 2 20000 2
ABCD 7 2500 3
ABCD NULL 32500 4
Pqrs 4 50500 1
Pqrs 5 36000 2
Pqrs 6 821 3
Pqrs 8 5000 4
Pqrs NULL 92321 5

(9 row(s) affected)
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources