Sql-server – Query to retrieve aggregated data using variables

querysql servert-sql

Summarise events using the MIN, MAX and COUNT functions

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.


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]