Can I join two aggregate queries? - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Can I join two aggregate queries?

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-01-18 : 09:00:57

I've got this query A


SELECT VehicleRef, Type, CapID, Manufacturer, Model, Derivative, CVehicle_ShortModText, MIN(CH) AS Expr1
FROM dbo.wvAllMatrixWithLombardAndShortModel
GROUP BY VehicleRef, Type, CapID, Manufacturer, Model, Derivative, CVehicle_ShortModText

Then i need to query the same table but addding a where


SELECT VehicleRef, Type, CapID, Manufacturer, Model, Derivative, CVehicle_ShortModText, MIN(CH) AS Expr2
FROM dbo.wvAllMatrixWithLombardAndShortModel
WHERE source = 'LOM'
GROUP BY VehicleRef, Type, CapID, Manufacturer, Model, Derivative, CVehicle_ShortModText

I need to return the results of both queries at the same time, so I should end up with one row for each capID with 2 CH prices (expr1 and expr2) - the link between the two queries is the CapID field.

Can this be done

Thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-18 : 09:07:37

One method is to hardcode an int field based on where condition so that you can retrive each query results seperately

SELECT VehicleRef, Type, CapID, Manufacturer, Model, Derivative, CVehicle_ShortModText, MIN(CH) AS Expr1,
CASE
WHEN source = 'LOM' THEN 1
ELSE 0
END
AS LOMSource
FROM dbo.wvAllMatrixWithLombardAndShortModel
GROUP BY VehicleRef, Type, CapID, Manufacturer, Model, Derivative, CVehicle_ShortModText

Then you may query on condition LOnSource=1/0 to retrieve seperated results of your each queries above

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2008-01-21 : 11:48:48

Hi,

Thanks for your help, but I dont follow? Is there anyway to merge the 2 queries into 1 - if not would it be possible in a stored proc?

Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-21 : 17:00:53

This should do what you want:

SELECT 
	VehicleRef, Type, CapID, Manufacturer, Model, Derivative, CVehicle_ShortModText, MAX(Expr1) AS Expr1, MAX(Expr2) AS Expr2
FROM
(
	SELECT VehicleRef, Type, CapID, Manufacturer, Model, Derivative, CVehicle_ShortModText, MIN(CH) AS Expr1, 0 AS Expr2
	FROM dbo.wvAllMatrixWithLombardAndShortModel
	GROUP BY VehicleRef, Type, CapID, Manufacturer, Model, Derivative, CVehicle_ShortModText

	UNION ALL

	SELECT VehicleRef, Type, CapID, Manufacturer, Model, Derivative, CVehicle_ShortModText, 0 AS Expr1, MIN(CH) AS Expr2
	FROM dbo.wvAllMatrixWithLombardAndShortModel
	WHERE source = 'LOM'
	GROUP BY VehicleRef, Type, CapID, Manufacturer, Model, Derivative, CVehicle_ShortModText
) AS Temp
GROUP BY
	VehicleRef, Type, CapID, Manufacturer, Model, Derivative, CVehicle_ShortModText

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources