CODEWITHSUNDEEP
sqlsql-servert-sql
Zoinky
Zoinky

Reputation: 5009

3 Statements into a view

I have 3 SQL statements that I would like to create a view and return 3 columns, each representing a count.

Here are my statements

SELECT Count(*) 
FROM PlaceEvents 
WHERE PlaceID = {placeID} AND EndDateTimeUTC >= GETUTCDATE()

SELECT Count(*) 
FROM PlaceAnnouncements 
WHERE PlaceID = {placeID}

SELECT Count(*) 
FROM PlaceFeedback 
WHERE PlaceID = {placeID} AND IsSystem = 0

I know how to create a basic view but how do I create one that will let me have those 3 column place placeID as a column to use for filtering

I would like to do the following to return the proper data

SELECT * 
FROM vMyCountView 
WHERE PlaceID = 1

CREATE VIEW vMyCountView AS
  (...) AS ActiveEvents,
  (...) AS Announcements,
  (...) AS UserFeedback,
  PlaceID

Upvotes: 1

Views: 68

Answers (4)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

I'd rather use a function then a view:

This allows you to pass in any parameters you like (I assumed placeId is an INT) and deal with it within your query. The handling is quite as easy as with a View:

CREATE FUNCTION MyCountFunction(@PlaceID INT)
RETURNS TABLE
AS
RETURN
SELECT
   (SELECT Count(*) FROM PlaceEvents WHERE PlaceID = @PlaceID AND EndDateTimeUTC >= GETUTCDATE()) AS ActiveEvents
  ,(SELECT Count(*) FROM PlaceAnnouncements WHERE PlaceID = @PlaceID) AS Announcements
  ,(SELECT Count(*) FROM PlaceFeedback WHERE PlaceID = @PlaceID AND IsSystem = 0) AS UserFeedback
  ,@PlaceID AS PlaceID;
GO

And this is how you call it. You can use this for JOINs or with APPLY also...

SELECT * FROM dbo.MyCountFunction(3);

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726509

You can make a view like that with GROUP BY:

SELECT
    PlaceId
,   Count(peId) AS ActiveEvents
,   COUNT(paId) AS Announcements
,   COUNT(fbId) AS UserFeedback
FROM (
    SELECT PlaceId, 1 AS peId, NULL AS paId, NULL AS fbId
    FROM PlaceEvents
    WHERE EndDateTimeUTC >= GETUTCDATE()
UNION ALL
    SELECT PlaceId, NULL AS peId, 1 AS paId, NULL AS fbId
    FROM PlaceAnnouncements
UNION ALL
    SELECT PlaceId, NULL AS peId, NULL AS paId, 1 AS fbId
    FROM PlaceFeedback
    WHERE IsSystem = 0
) src
GROUP BY PlaceId

The idea behind this select, which is very easy to make into a view, is to select items from three tables into one for counting, and then group them all at once.

If you have two active events, one announcement, and three feedbacks for place ID 123, the three inner selects would produce this:

PlaceId  peId  paId  fbId
-------  ----  ----  ----
    123     1  NULL  NULL
    123     1  NULL  NULL
    123  NULL     1  NULL
    123  NULL  NULL     1
    123  NULL  NULL     1
    123  NULL  NULL     1

Upvotes: 0

Alex Kudryashev
Alex Kudryashev

Reputation: 9460

By definition, view is a single select statement. You can use join, union and so on if it makes sense to your business logic provided create view is the only query in the batch.

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can combine them as multiple select sub-queries. 

CREATE VIEW vMyCountView AS
SELECT
(SELECT Count(*) FROM PlaceEvents 
 WHERE PlaceID = s.placeID AND EndDateTimeUTC >= GETUTCDATE()) AS ActiveEvents,
(SELECT Count(*) FROM PlaceAnnouncements 
 WHERE PlaceID = s.placeID) AS Announcements,
(SELECT Count(*) FROM PlaceFeedback 
 WHERE PlaceID = s.placeID AND IsSystem = 0) AS UserFeedback,
 placeID
from Sometable s

Upvotes: 0

Related Questions