Combine three aggregate queries into one? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Combine three aggregate queries into one?

Author

Topic

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2008-07-07 : 16:32:28

I have three queries that each return a SUM; is it possible to combine the three so that each SUM is returned as a column in a single table?

Right now, the user selects dates from a pair of Calendar controls, then I pass those dates to three different functions that in turn fetch the single values from these queries; the resulting data is displayed in three separate DataGrids. It works, but I don't like the way it looks. If I can meld the three into one query, then I would be looking at something I can work with.

I've done something similar before, but it was with two queries of the exact same tables, and no aggregate data.

The queries look like:

DECLARE @dtFromDate as varchar(40), @dtToDate AS varchar(40)
SET @dtFromDate = '2008-07-01 00:00:00.000'
SET @dtToDate = '2008-07-07 00:00:00.000'

SELECT SUM(sorels.funetprice * (shitem.fshipqty - shitem.finvqty)) AS TSNI 
FROM shmast, shitem LEFT JOIN 
sorels on sorels.fsono + sorels.finumber + sorels.frelease = shitem.fsokey 
WHERE Shitem.fshipno = Shmast.fshipno 
AND fconfirm = 'Y'
AND ftype NOT IN ('JO', 'VE', 'MI') 
AND shitem.fitemtype <> 'M' 
AND shmast.flisinv = 0 
AND fcstatus<> 'C' 
AND LEN(shitem.idono) = 0 
and shitem.fshipqty > 0 
AND shmast.fshipdate BETWEEN @dtFromDate and @dtToDate

SELECT	SUM((sorels.forderqty - (sorels.fshipbook + sorels.fShipBuy + sorels.fshipmake)) * sorels.funetprice) as TYTS 
FROM sorels 
JOIN somast ON somast.fsono = sorels.fsono 
JOIN soitem ON soitem.fsono = sorels.fsono AND soitem.finumber = sorels.finumber 
LEFT OUTER JOIN inmast ON inmast.fpartno = soitem.fpartno 
AND inmast.frev = soitem.fpartrev 
AND inmast.fac = soitem.fac 
WHERE sorels.fmasterrel = 0 AND somast.fstatus = 'Open' 
And (sorels.forderqty - sorels.fshipbook - sorels.fShipBuy - sorels.fshipmake) >  0 
AND sorels.fduedate between @dtFromDate and @dtToDate 


SELECT SUM(aritem.ftotprice) AS TI 
FROM aritem join armast on aritem.fcinvoice = armast.fcinvoice  
WHERE finvdate between @dtFromDate and @dtToDate
AND aritem.fpartno NOT IN ('SHIPPING')
AND aritem.fcinvoice NOT LIKE ('CM%') 
AND aritem.fcinvoice NOT LIKE ('PM%') 
AND aritem.fsokey NOT IN ('')
AND armast.fcstatus NOT IN ('V')

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-07 : 19:36:18

the 3 query are completely not related. It will not be easy to combine into single query.

You can assign the result to variable and select the variable out as a result

select @var1 = sum( .. . )
select @var2 = sum( . . .)
select @var3 = sum(aritem.ftotprice) . . .

select @var1 as TSNI , @var2 as TYTS, @var3 as TI

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

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-07-08 : 02:26:58

You can use a Cartesian product since your queries are only returning one value each.
Example.


SELECT X.Field
     , Y.Field
     , Z.Field 
FROM 
      (SELECT * FROM yourtable1) X
     ,(SELECT * FROM Yourtable2) Y
     ,(SELECT * FROM Yourtable3) Z

And this is your code.


SELECT TSNI, TYTS, TI
FROM 
(SELECT SUM(sorels.funetprice * (shitem.fshipqty - shitem.finvqty)) AS TSNI 
FROM shmast, shitem LEFT JOIN 
sorels on sorels.fsono + sorels.finumber + sorels.frelease = shitem.fsokey 
WHERE Shitem.fshipno = Shmast.fshipno 
AND fconfirm = 'Y'
AND ftype NOT IN ('JO', 'VE', 'MI') 
AND shitem.fitemtype <> 'M' 
AND shmast.flisinv = 0 
AND fcstatus<> 'C' 
AND LEN(shitem.idono) = 0 
and shitem.fshipqty > 0 
AND shmast.fshipdate BETWEEN @dtFromDate and @dtToDate) As tbl_TSNI , 

(SELECT	SUM((sorels.forderqty - (sorels.fshipbook + sorels.fShipBuy + sorels.fshipmake)) * sorels.funetprice) as TYTS 
FROM sorels 
JOIN somast ON somast.fsono = sorels.fsono 
JOIN soitem ON soitem.fsono = sorels.fsono AND soitem.finumber = sorels.finumber 
LEFT OUTER JOIN inmast ON inmast.fpartno = soitem.fpartno 
AND inmast.frev = soitem.fpartrev 
AND inmast.fac = soitem.fac 
WHERE sorels.fmasterrel = 0 AND somast.fstatus = 'Open' 
And (sorels.forderqty - sorels.fshipbook - sorels.fShipBuy - sorels.fshipmake) >  0 
AND sorels.fduedate between @dtFromDate and @dtToDate ) As tbl_TYTS , 


(SELECT SUM(aritem.ftotprice) AS TI 
FROM aritem join armast on aritem.fcinvoice = armast.fcinvoice  
WHERE finvdate between @dtFromDate and @dtToDate
AND aritem.fpartno NOT IN ('SHIPPING')
AND aritem.fcinvoice NOT LIKE ('CM%') 
AND aritem.fcinvoice NOT LIKE ('PM%') 
AND aritem.fsokey NOT IN ('')
AND armast.fcstatus NOT IN ('V')) Tbl_TI

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 04:35:27

How do you want to show the figures? in rows or columns?

Metcalf
Yak Posting Veteran

52 Posts

Posted - 2008-07-08 : 09:55:53

quote:
Originally posted by Dallr

You can use a Cartesian product since your queries are only returning one value each.
Example.

SELECT X.Field
, Y.Field
, Z.Field
FROM
(SELECT * FROM yourtable1) X
,(SELECT * FROM Yourtable2) Y
,(SELECT * FROM Yourtable3) Z

Oh, that's awesome. I didn't think I could select from all three at the same time without relating them somehow. Obviously, I wouldn't want the Cartesian Product if I were trying to get details, but seeing it laid out like you have, it makes sense when grabbing a single bit of information from each table.

Thanks for pointing this out!! Best part is, it worked perfectly. Very Nice!!

Dallr
Yak Posting Veteran

87 Posts

Posted - 2008-07-08 : 15:50:55

Yes you are correct, if any one of the queries were returning more than one record the Cartesian will not work for the results you need in this case

Glad it is working for you!

Dallr

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources