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

Transact-SQL (2005)

Grouping query

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-13 : 03:21:25

I am trying to join two tables 'SupplierTranaction' and 'BankAccountPortfolios' by the field 'PortfolioCode' and then group the results by 'SourceID' where the SupplierCode = 'LAN001'.

So far I have:

use debt
select t.ID, t.SourceID, p.BankAccountCode, t.TranDate, t.Period,
t.Year, t.TranType, t.Reference, t.PortfolioCode, t.Description,
SUM(NetAmount) AS NetAmount, SUM(TaxAmount) AS TaxAmount,
SUM(PaidAmount) AS PaidAmount, t.SupplierCode
from SupplierTransaction as t

JOIN BankAccountPortfolios AS p
ON t.PortfolioCode = p.PortfolioCode

where supplierCode = 'LAN001'

Group By t.SourceID

without success. What should the correct syntax by please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-13 : 03:47:33

[code]USE Debt
GO

SELECT t.ID,
t.SourceID,
p.BankAccountCode,
t.TranDate,
t.Period,
t.Year,
t.TranType,
t.Reference,
t.PortfolioCode,
t.Description,
SUM(t.NetAmount) AS NetAmount,
SUM(t.TaxAmount) AS TaxAmount,
SUM(t.PaidAmount) AS PaidAmount,
t.SupplierCode
FROM SupplierTransaction AS t
INNER JOIN BankAccountPortfolios AS p ON p.PortfolioCode = t.PortfolioCode
WHERE t.supplierCode = 'LAN001'
GROUP BY t.ID,
t.SourceID,
p.BankAccountCode,
t.TranDate,
t.Period,
t.Year,
t.TranType,
t.Reference,
t.PortfolioCode,
t.Description[/code]

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-13 : 04:46:11

try like this
select t.ID, t.SourceID, p.BankAccountCode, t.TranDate, t.Period,
t.Year, t.TranType, t.Reference, t.PortfolioCode, t.Description,
s.NetAmount, s.TaxAmount,
s.PaidAmount, t.SupplierCode
from SupplierTransaction as t

JOIN BankAccountPortfolios AS p
ON t.PortfolioCode = p.PortfolioCode
JOIN
(
select SourceID, SUM(NetAmount) AS NetAmount, SUM(TaxAmount) AS TaxAmount,
SUM(PaidAmount) AS PaidAmount from SupplierTransaction Group By SourceID
) s
ON s.sourceid = t.sourceid
where supplierCode = 'LAN001'

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-13 : 05:28:39

Thanks for the replies, but this did not give me what I require.

So, in order to help me understand better here is an example I need help with please:

I have a table:

SourceID  Date        Type  NetAmount  TaxAmount PaidAmount
--------  ----        ----  ---------  --------- ----------
101       02/04/2008  INV   100.00     0.00      100.00
121       03/04/2008  INV   56.00      0.00      56.00
101       02/04/2008  INV   17.00      0.00      17.00

I want to return all the fields grouped by the SourceID with the NetAmount, TaxAmount and PaidAmount fields being a SUM to give me

SourceID  Date        Type  NetAmount  TaxAmount PaidAmount
--------  ----        ----  ---------  --------- ----------
101       02/04/2008  INV   117.00     0.00      117.00
121       03/04/2008  INV   56.00      0.00      56.00

what should the query be please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-02-13 : 05:47:00

A normal GROUP BY query.

DECLARE	@Sample TABLE
	(
		SourceID INT,
		[Date] DATETIME,
		[Type] CHAR(3),
		NetAmount MONEY,
		TaxAmount MONEY,
		PaidAmount MONEY
	)

INSERT	@Sample
SELECT	101, '02/04/2008', 'INV', 100.00, 0.00, 100.00 UNION ALL
SELECT	121, '03/04/2008', 'INV',  56.00, 0.00,  56.00 UNION ALL
SELECT	101, '02/04/2008', 'INV',  17.00, 0.00,  17.00

SELECT	*
FROM	@Sample

SELECT		SourceID,
		[Date],
		[Type],
		SUM(NetAmount) AS NetAmount,
		SUM(TaxAmount) AS TaxAmount,
		SUM(PaidAmount) AS PaidAmount
FROM		@Sample
GROUP BY	SourceID,
		[Date],
		[Type]

E 12°55'05.63"
N 56°04'39.26"

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-13 : 06:31:58

Many thanks for that example Peso. Education isa wonderful thing - it helped me a lot.

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-13 : 09:19:04

quote:
Originally posted by bklr

try like this
select t.ID, t.SourceID, p.BankAccountCode, t.TranDate, t.Period,
t.Year, t.TranType, t.Reference, t.PortfolioCode, t.Description,
s.NetAmount, s.TaxAmount,
s.PaidAmount, t.SupplierCode
from SupplierTransaction as t

JOIN BankAccountPortfolios AS p
ON t.PortfolioCode = p.PortfolioCode
JOIN
(
select SourceID, SUM(NetAmount) AS NetAmount, SUM(TaxAmount) AS TaxAmount,
SUM(PaidAmount) AS PaidAmount from SupplierTransaction Group By SourceID
) s
ON s.sourceid = t.sourceid
where supplierCode = 'LAN001'

This suggestion gave the correctly SUMmed fields but the rows are generated multiple times, instead of a single row with the correct SUMmed totals shown.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 09:31:33

quote:
Originally posted by OldMySQLUser

quote:
Originally posted by bklr

try like this
select t.ID, t.SourceID, p.BankAccountCode, t.TranDate, t.Period,
t.Year, t.TranType, t.Reference, t.PortfolioCode, t.Description,
s.NetAmount, s.TaxAmount,
s.PaidAmount, t.SupplierCode
from SupplierTransaction as t

JOIN BankAccountPortfolios AS p
ON t.PortfolioCode = p.PortfolioCode
JOIN
(
select SourceID, SUM(NetAmount) AS NetAmount, SUM(TaxAmount) AS TaxAmount,
SUM(PaidAmount) AS PaidAmount from SupplierTransaction Group By SourceID
) s
ON s.sourceid = t.sourceid
where supplierCode = 'LAN001'

This suggestion gave the correctly SUMmed fields but the rows are generated multiple times, instead of a single row with the correct SUMmed totals shown.

that beacuse suggestion is just joining to main table without grouping by in main query. so it will get repeated for each record in table with same group value

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-13 : 09:42:11

quote:
Originally posted by visakh16

quote:
Originally posted by OldMySQLUser

quote:
Originally posted by bklr

try like this
select t.ID, t.SourceID, p.BankAccountCode, t.TranDate, t.Period,
t.Year, t.TranType, t.Reference, t.PortfolioCode, t.Description,
s.NetAmount, s.TaxAmount,
s.PaidAmount, t.SupplierCode
from SupplierTransaction as t

JOIN BankAccountPortfolios AS p
ON t.PortfolioCode = p.PortfolioCode
JOIN
(
select SourceID, SUM(NetAmount) AS NetAmount, SUM(TaxAmount) AS TaxAmount,
SUM(PaidAmount) AS PaidAmount from SupplierTransaction Group By SourceID
) s
ON s.sourceid = t.sourceid
where supplierCode = 'LAN001'

This suggestion gave the correctly SUMmed fields but the rows are generated multiple times, instead of a single row with the correct SUMmed totals shown.

that beacuse suggestion is just joining to main table without grouping by in main query. so it will get repeated for each record in table with same group value

I had tried

use debt

select t.ID, t.SourceID, p.BankAccountCode, t.TranDate, t.Period,
t.Year, t.TranType, t.Reference, t.PortfolioCode, t.Description,
s.NetAmount, s.TaxAmount,
s.PaidAmount, t.SupplierCode
from SupplierTransaction as t

JOIN BankAccountPortfolios AS p
ON t.PortfolioCode = p.PortfolioCode
JOIN
(
select SourceID, SUM(NetAmount) AS NetAmount, SUM(TaxAmount) AS TaxAmount,
SUM(PaidAmount) AS PaidAmount from SupplierTransaction Group By SourceID
) s
ON s.sourceid = t.sourceid
Group By t.ID, t.SourceID, p.BankAccountCode, t.TranDate, t.Period,
t.Year, t.TranType, t.Reference, t.PortfolioCode, t.Description,
s.NetAmount, s.TaxAmount,
s.PaidAmount, t.SupplierCode

having(t.SupplierCode = 'LAN001')

order by reference

without success. What *should* I have done to get this right please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 09:47:33

i think it should have been something like this


select s.ID, s.SourceID, p.BankAccountCode, s.TranDate, s.Period,
s.Year, s.TranType, s.Reference, s.PortfolioCode,s.Description,
s.NetAmount, s.TaxAmount,
s.PaidAmount, s.SupplierCode
from  BankAccountPortfolios AS p
JOIN
(
select ID,TranDate, Period,
Year, TranType, Reference, PortfolioCode, Description,SourceID,SupplierCode, SUM(NetAmount) AS NetAmount, SUM(TaxAmount) AS TaxAmount,
SUM(PaidAmount) AS PaidAmount from SupplierTransaction 
Group By ID,TranDate, Period,
Year, TranType, Reference, PortfolioCode, Description,SourceID,SupplierCode
) s
ON s.sourceid = t.sourceid
where supplierCode = 'LAN001'

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-13 : 10:03:59

quote:

Originally posted by visakh16

i think it should have been something like this


select s.ID, s.SourceID, p.BankAccountCode, s.TranDate, s.Period,
s.Year, s.TranType, s.Reference, s.PortfolioCode,s.Description,
s.NetAmount, s.TaxAmount,
s.PaidAmount, s.SupplierCode
from  BankAccountPortfolios AS p
JOIN
(
select ID,TranDate, Period,
Year, TranType, Reference, PortfolioCode, Description,SourceID,SupplierCode, SUM(NetAmount) AS NetAmount, SUM(TaxAmount) AS TaxAmount,
SUM(PaidAmount) AS PaidAmount from SupplierTransaction 
Group By ID,TranDate, Period,
Year, TranType, Reference, PortfolioCode, Description,SourceID,SupplierCode
) s
ON s.sourceid = t.sourceid
where supplierCode = 'LAN001'

This now gives me 'The multi-part identifier "t.sourceid" could not be bound.' I tried to add AS t to the 'SUM(PaidAmount) AS PaidAmount from SupplierTransaction' but that wasn't it. Any pointers much appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-13 : 11:07:11

[code]
select s.ID, s.SourceID, p.BankAccountCode, s.TranDate, s.Period,
s.Year, s.TranType, s.Reference, s.PortfolioCode,s.Description,
s.NetAmount, s.TaxAmount,
s.PaidAmount, s.SupplierCode
from BankAccountPortfolios AS p
JOIN
(
select ID,TranDate, Period,
Year, TranType, Reference, PortfolioCode, Description,SourceID,SupplierCode, SUM(NetAmount) AS NetAmount, SUM(TaxAmount) AS TaxAmount,
SUM(PaidAmount) AS PaidAmount from SupplierTransaction
Group By ID,TranDate, Period,
Year, TranType, Reference, PortfolioCode, Description,SourceID,SupplierCode
) s
ON s.PortfolioCode = p.PortfolioCode
where supplierCode = 'LAN001'
[/code]

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-14 : 07:12:55

Unfortunately, I still get multiple rows returned

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 10:54:02

so what is want is unique record per SourceID, Date, Type group?

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-14 : 11:18:39

I need to return the number of columns shown in the script. But in reality we only need to group on sourceID.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 11:38:57

[code]select t.ID, t.SourceID, p.BankAccountCode, t.TranDate, t.Period,
t.Year, t.TranType, t.Reference, t.PortfolioCode, t.Description,
SUM(NetAmount) OVER (PARTITION BY t.SourceID) AS NetAmount, SUM(TaxAmount) OVER (PARTITION BY t.SourceID) AS TaxAmount,
SUM(PaidAmount) OVER (PARTITION BY t.SourceID) AS PaidAmount, t.SupplierCode
from SupplierTransaction as t
JOIN BankAccountPortfolios AS p
ON t.PortfolioCode = p.PortfolioCode
where supplierCode = 'LAN001'
[/code]

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2009-02-14 : 11:45:49

Many thanks for all your suggestions so far, but I am still getting multiple rows. Grrrrr .....

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-14 : 11:52:55

[code]
SELECT all columns except Seq here...
FROM
(
select t.ID, t.SourceID, p.BankAccountCode, t.TranDate, t.Period,
t.Year, t.TranType, t.Reference, t.PortfolioCode, t.Description,
SUM(NetAmount) OVER (PARTITION BY t.SourceID) AS NetAmount, SUM(TaxAmount) OVER (PARTITION BY t.SourceID) AS TaxAmount,
SUM(PaidAmount) OVER (PARTITION BY t.SourceID) AS PaidAmount, t.SupplierCode,
ROW_NUMBER() OVER (PARTITION BY t.SourceID ORDER BY t.ID
) AS Seq
from SupplierTransaction as t
JOIN BankAccountPortfolios AS p
ON t.PortfolioCode = p.PortfolioCode
where supplierCode = 'LAN001'
)t
WHERE Seq=1
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources