Sql-server – Query to retrieve aggregated data using variables

querysql servert-sql

Question:
Summarise events using the MIN, MAX and COUNT functions

Help:
To do this, create variables to hold each of the aggregates:

Variable          What it will hold eventually
---------------   -----------------------------  
@EarliestDate   = The earliest date
@LatestDate     = The latest date
@NumberOfEvents = The number of events
@EventInfo      = The title Summary of events

Now use a single SELECT clause to set the value for all of these variables (alternatively you could use a series of subqueries), write another SELECT clause to show the value of the variables, to show the summary output shown above.

Result:

enter image description here

I'm having trouble getting this exercise done.

Is there anywhere I can get the solution?

Thanks in advance!

Best Answer

Something like this?

declare @EarliestDate datetime
declare @LatestDate datetime
declare @NumberOfEvents int
declare @EventInfo nvarchar(100) = 'Summary of events'

SELECT   @EarliestDate = (SELECT MIN(Dtm) FROM [YourTable]) 
        ,@LatestDate = (SELECT MAX(Dtm) FROM [YourTable]) 
        ,@NumberOfEvents = (SELECT COUNT(*) FROM [YourTable]) 

SELECT   @EventInfo [Title]
        ,@EarliestDate [Earliest Date]
        ,@LatestDate [Latest date]
        ,@NumberOfEvents [Number of Events]
Related Question