Sum and count of different tables

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 and count of different tables

Author

Topic

itzmohikumar
Starting Member

3 Posts

Posted - 2007-09-22 : 06:18:59

I have the following four tables wherein MEMACT is the parent table for all the below tables.

MEMACT
--------
ACTNUM VARCHAR2(10) NOT NULL
ACTCOD VARCHAR2(5) NOT NULL

MEMPNTTXN
---------
ACTNUM VARCHAR2(10) NOT NULL
TXNNUM NUMERIC(5 , 0) NOT NULL
PNT NUMERIC(10 , 2) NOT NULL

CHGHEDDTL
------------
ACTNUM VARCHAR2(10) NOT NULL
CHGCOD VARCHAR2(5) NOT NULL
AMT NUMERIC(10 , 2) NOT NULL

PAYMNT
------
ACTNUM VARCHAR2(10) NOT NULL
PAYTYPCOD VARCHAR2(5) NOT NULL
AMT NUMERIC(10 , 2) NOT NULL

I will have approximately one million records in each table.

I want to write a query which should return the following output (grouping based on MEMACT.ACTCOD)

Output:
Count(MEMACT.ACTNUM), SUM(MEMPNTTXN.PNT), SUM(CHGHEDDTL.AMT), SUM(PAYMNT.AMT).

If we write a inner query for each child table, will this not effect the performance of the query exceution?

Can anybody please, help me on sorting out this problem.

Kristen
Test

22859 Posts

Posted - 2007-09-22 : 06:31:27

VARCHAR2 looks like Oracle to me, in which case you may not get much help here as this is a SQL Server only forum. Perhaps try dbforums isntead.

In SQL Server I would do:


SELECT	MA.ACTNUM, PT.[PNT_Total], CD.[AMT_Total], P.[AMT_Total]
FROM	MEMACT AS MA
	LEFT OUTER JOIN
	(
		SELECT	ACTNUM, [PNT_Total] = SUM(PNT)
		FROM	MEMACT AS MA
			JOIN MEMPNTTXN AS PT
				ON PT.ACTNUM = MA.ACTNUM
		WHERE	... criteria on MEMACT
	) AS PT
		ON PT.ACTNUM = MA.ACTNUM
	LEFT OUTER JOIN
	(
		SELECT	ACTNUM, [AMT_Total] = SUM(AMT)
		FROM	MEMACT AS MA
			JOIN CHGHEDDTL AS CD
				ON CD.ACTNUM = MA.ACTNUM
		WHERE	... criteria on MEMACT
	) AS CD
		ON CD.ACTNUM = MA.ACTNUM
	LEFT OUTER JOIN
	(
		SELECT	ACTNUM, [AMT_Total] = SUM(AMT)
		FROM	MEMACT AS MA
			JOIN PAYMNT AS P
				ON P.ACTNUM = MA.ACTNUM
		WHERE	... criteria on MEMACT
	) AS P
		ON P.ACTNUM = MA.ACTNUM
WHERE	... criteria on MEMACT

Kristen

itzmohikumar
Starting Member

3 Posts

Posted - 2007-09-24 : 04:00:11

Thanks kristen

Will this not effect the performance of the query excution?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-24 : 04:04:25

Yes. It will be faster and more accurate.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-24 : 04:36:23

And if there no filters on MEMACT table, this will perform better

SELECT		m.ActNum,
		m.ActCod
FROM		MemAct AS m
LEFT JOIN	(
			SELECT		ActNum,
					SUM(Pnt) AS Pnt
			FROM		MEMPNTTXN
			GROUP BY	ActNum
		) AS t1 ON t1.ActNum = m.Actnum
LEFT JOIN	(
			SELECT		ActNum,
					SUM(Amt)
			FROM		CHGHEDDTL
			GROUP BY	ActNum
		) AS t2 ON t2.ActNum = m.ActNum
LEFT JOIN	(
			SELECT		ActNum,
					SUM(Amt)
			FROM		PAYMNT
			GROUP BY	ActNum
		) AS t3 ON t3.ActNum = m.ActNum

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources