CODEWITHSUNDEEP
sql
Jake Sankey
Jake Sankey

Reputation: 5137

Return sum of values in column from a subquery

Ok, So I have this query that is creating a new column (total) which is the timespan (in minutes) between StartDateTime and EndDateTime. What I really want to do is not to return these few numbers but return only the sum of these few numbers. (basically the query will return one number, the sum of the 'Total' column). See the snapshot below and I also put the query here for you to modify. Thanks!

SELECT     ID, StartDateTime, EndDateTime, DATEDIFF(mi, StartDateTime, EndDateTime) AS Total
FROM         tEvent
WHERE     (EventDefinitionID = '1427' OR
                      EventDefinitionID = '1428' OR
                      EventDefinitionID = '1429' OR
                      EventDefinitionID = '1430' OR
                      EventDefinitionID = '1432' OR
                      EventDefinitionID = '1434' OR
                      EventDefinitionID = '1435' OR
                      EventDefinitionID = '1436' OR
                      EventDefinitionID = '1437' OR
                      EventDefinitionID = '1438' OR
                      EventDefinitionID = '1439' OR
                      EventDefinitionID = '1440' OR
                      EventDefinitionID = '1441' OR
                      EventDefinitionID = '1442' OR
                      EventDefinitionID = '1443' OR
                      EventDefinitionID = '1444' OR
                      EventDefinitionID = '1445' OR
                      EventDefinitionID = '1446') AND (EndDateTime IS NOT NULL) AND (DAY(StartDateTime) = DAY(GETDATE()))
ORDER BY StartDateTime DESC

enter image description here

Upvotes: 1

Views: 221

Answers (2)

Michael Goldshteyn
Michael Goldshteyn

Reputation: 74360

If I understood the question correctly, just change your query to the following to only calculate a single number, the grand total of all of the totals. Slightly simplified and reformatted, here is the query:

SELECT     SUM(DATEDIFF(mi, StartDateTime, EndDateTime)) AS GrandTotal 
FROM       tEvent 
WHERE     (EventDefinitionID IN ('1427','1428','1429','1430','1432','1434',
                                 '1435','1436','1437','1438','1439','1440',
                                 '1441','1442','1443','1444','1445'))
       AND EndDateTime IS NOT NULL
       AND DAY(StartDateTime) = DAY(GETDATE())

If your EventDefinitionID is numeric, you can make the query even more readable by using BETWEEN:

SELECT     SUM(DATEDIFF(mi, StartDateTime, EndDateTime)) AS GrandTotal 
FROM       tEvent 
WHERE     (EventDefinitionID BETWEEN 1427 AND 1430
        OR EventDefinitionID = 1432
        OR EventDefinitionID BETWEEN 1434 AND 1445)
       AND EndDateTime IS NOT NULL
       AND DAY(StartDateTime) = DAY(GETDATE())

Upvotes: 3

yoprogramo
yoprogramo

Reputation: 1306

try

SELECT     SUM(DATEDIFF(mi, StartDateTime, EndDateTime)) AS Total
FROM         tEvent
WHERE     (EventDefinitionID = '1427' OR
                      EventDefinitionID = '1428' OR
                      EventDefinitionID = '1429' OR
                      EventDefinitionID = '1430' OR
                      EventDefinitionID = '1432' OR
                      EventDefinitionID = '1434' OR
                      EventDefinitionID = '1435' OR
                      EventDefinitionID = '1436' OR
                      EventDefinitionID = '1437' OR
                      EventDefinitionID = '1438' OR
                      EventDefinitionID = '1439' OR
                      EventDefinitionID = '1440' OR
                      EventDefinitionID = '1441' OR
                      EventDefinitionID = '1442' OR
                      EventDefinitionID = '1443' OR
                      EventDefinitionID = '1444' OR
                      EventDefinitionID = '1445' OR
                      EventDefinitionID = '1446') AND (EndDateTime IS NOT NULL) AND (DAY(StartDateTime) = DAY(GETDATE()))

Upvotes: 1

Related Questions