Join two queries into one result set

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)

Join two queries into one result set

Author

Topic

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-08-29 : 12:00:05

I have this

SELECT CVehicle_mantext,COUNT(Cvehicle_mantext) as Count FROM PUB_CAR..CapVehicles WHERE CVehicle_id IN (SELECT CapID FROM dbPubMatrix..tblCarImages WHERE noimage=1)
GROUP BY CVehicle_mantext
ORDER BY Count Desc

How can I get an additional column in the results which is the result of the COUNT(Cvehicle_mantext) as above except with noimage=0

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 12:17:25

This, maybe?

SELECT		cv.CVehicle_mantext,
		SUM(CASE WHEN ci.NoImage = 1 THEN 1 ELSE 0 END) AS [Count1],
		SUM(CASE WHEN ci.NoImage = 1 THEN 0 ELSE 1 END) AS [Count2]
FROM		PUB_CAR..CapVehicles AS cv
LEFT JOIN	dbPubMatrix..tblCarImages AS ci ON ci.CapID = cv.CVehicle_id
GROUP BY	cv.CVehicle_mantext
ORDER BY	cv.CVehicle_mantext

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

Mondeo
Constraint Violating Yak Guru

287 Posts

Posted - 2007-08-29 : 12:32:43

Thanks!

Taking it a step furthur I tried to add a column for the total images

SELECT cv.CVehicle_mantext,
SUM(CASE WHEN ci.NoImage = 1 THEN 1 ELSE 0 END) AS [NoImage],
SUM(CASE WHEN ci.NoImage = 1 OR ci.NoImage IS NULL THEN 0 ELSE 1 END) AS [HasImage],
SUM([NoImage]+[HasImage]) AS [Total]
FROM PUB_CAR..CapVehicles AS cv
LEFT JOIN dbPubMatrix..tblCarImages AS ci ON ci.CapID = cv.CVehicle_id
GROUP BY cv.CVehicle_mantext
ORDER BY NoImage DESC

Error - Invalid column name 'HasImage'.

Wrong syntax?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-29 : 12:43:54

[/code]SELECT cv.CVehicle_mantext,
SUM(CASE WHEN ci.NoImage = 1 THEN 1 ELSE 0 END) AS [Count1],
SUM(CASE WHEN ci.NoImage = 1 THEN 0 ELSE 1 END) AS [Count2],
COUNT(*) AS Total
FROM PUB_CAR..CapVehicles AS cv
LEFT JOIN dbPubMatrix..tblCarImages AS ci ON ci.CapID = cv.CVehicle_id
GROUP BY cv.CVehicle_mantext
ORDER BY cv.CVehicle_mantext[/code]

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