Web Development

Ask something
Question Locked

query to add tow counts

0 helpful
3 replies

by sarasami

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi every one,

I'm looking for a query that adds two counts together,
my queries are:

(SELECT distinct Room. [Room Type] AS [**Room Type** ] , COUNT (distinct Room.[Room No]) AS [No. Of Rooms Left]
FROM SiteHotel INNER JOIN Room ON SiteHotel.HotelID = Room.HotelID INNER JOIN Reserve ON Room.[Room No] = Reserve.[Room No]
WHERE (@Sdate NOT BETWEEN Reserve.[Chick-in] AND Reserve.[Chick-out]) AND (@Edate NOT BETWEEN Reserve.[Chick-in] AND Reserve.[Chick-out]) AND (SiteHotel.City = @City) AND (Reserve.[Chick-in] NOT BETWEEN @Sdate AND @Edate) AND (Reserve.[Chick-out] NOT BETWEEN @Sdate AND @Edate) OR(@Sdate = Reserve.[Chick-out]) AND (@Edate = Reserve.[Chick-in]) AND (Reserve.[Chick-in] = @Edate) AND (Reserve.[Chick-out] = @Sdate)
GROUP BY [Room Type] )

(SELECT distinct Room. [Room Type] AS [**Room Type** ] , COUNT (distinct Room.[Room No]) AS [No. Of Rooms Left]
FROM SiteHotel INNER JOIN Room ON SiteHotel.HotelID = Room.HotelID
WHERE (SiteHotel.City = @City) AND (Room.[Room No] NOT IN
(SELECT [Room No]
FROM Reserve
WHERE (SiteHotel.HotelID = HotelID)))
GROUP BY [Room Type] )

and I want to merge these two queries together ,and adds the two counts and put the result in one column
Thanks in advance

3 replies

by thumbsup2

You can't do what you want to do in the queries themselves.

Store the results of each query in a variable array. Then use each array to build your new merged table/column and add the calculations needed to generate the counts.

by tony hopkinson

Select [**Room Type**] , Sum([No. Of Rooms Left])
From (MessyQueryOne UNION MessyQueryTwo) MessyQueriesOneAndTwo
Group By [**Room Type**]

Should do the job.

I'd start looking at performance if I were you, the DBMS's query optimiser will start giving up on you soon.

This discussion is closed to new replies.