CODEWITHSUNDEEP
sqlsql-server
dcoffin
dcoffin

Reputation: 33

SELECT statement to find SUM of a column

I am trying to create a query statement that will find sum of the TaskLength column.

I've tried quite a number combinations placing the SQL SUM function in the query using comma separators, and AND statements. Without the SUM TaskLength, the query works fine.

This is being used in a c# WPF application but I'm able to test the query statements inside the Visual Studio, Server Explorer query window.

Here is the query I have tried:

SELECT TaskName, TaskLength FROM myTable, SUM TaskLength 
WHERE TaskName='Maintenance' AND EventStartTime 
BETWEEN '8/17/2019' AND '8/19/2019'

This is my table:

TABLE myTable 
ID    TaskName      EventStartTime          TaskLength
318   START SHIFT   8/17/2019 11:51:00 AM   16                  
319   Maintenance   8/17/2019 12:07:00 PM   10                  
320   Part Prep     8/17/2019 12:17:00 PM   13                  
321   Production    8/17/2019 12:30:00 PM   117                 
322   Part Prep     8/17/2019 02:26:00 PM   45                  
323   Downtime      8/17/2019 02:28:00 PM   65                  
327   START SHIFT   8/17/2019 03:48:00 PM   00                  
328   Maintenance   8/17/2019 03:48:00 PM   25                  
329   Part Prep     8/17/2019 03:48:00 PM   35                  
330   Production    8/17/2019 03:48:00 PM   120                 
331   START SHIFT   8/17/2019 03:59:00 PM   10                  
332   Maintenance   8/17/2019 03:59:00 PM   90

I am getting Incorrect Syntax Near.. error. I expect when it works out correctly I will get a result of 125.

Revised query with entire code block:

using (SqlConnection con = new SqlConnection(_connectionString))
{
    string query = @"SELECT TaskName, SUM(TaskLength) as TaskLength FROM myTable " +
          "WHERE EventStartTime BETWEEN '2019/8/17' AND '2019/8/19' GROUP BY TaskName";

      using (SqlCommand cmd = new SqlCommand(query, con))
      {
          con.Open();

          object result = cmd.ExecuteScalar();  // returns 'Downtime'
          string totalMinutes = Convert.ToString(result);

      }
}

I'm progressing along. The code executes without errors except the result returns "Downtime" from the TaskName column instead of 125 from SUM(TaskLength) that I expect.

This query does work correctly in the Visual Studio SQL query window.

Upvotes: 0

Views: 211

Answers (5)

Shekar Kola
Shekar Kola

Reputation: 1297

Although, answer to this question is SUM () and Group by, just want bring following points into consideration as I have not noticed in any answer:

  1. Always use date format yyyy-mm-dd when you manually passing the values in filter or any other place in query, otherwise SQL engine might through error Conversion failed when converting date and/or time from character string. this would happen in case if the SQL Server environmental setup different than the format date that we used i.e. query format MM-DD-YYYY server environmental setup format DD-MM-YYYY.
  2. When you do scalar functions or expressions (in our case sum), column label would be wiped-out, be sure to mentioned as with column label - it is good practice, your query would work without alias
SELECT TaskName, SUM(TaskLength) as TaskLength
FROM myTable 
WHERE TaskName = 'Maintenance' AND
      EventStartTime between '2019-08-17' AND '2019-08-20'
GROUP BY TaskName

Updated as per comment

SELECT SUM(TaskLength) as TaskLength
FROM myTable 
WHERE TaskName = 'Maintenance' AND
      EventStartTime between '2019-08-17' AND '2019-08-20'

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269773

I think you want a GROUP BY query:

SELECT TaskName, SUM(TaskLength)
FROM myTable 
WHERE TaskName = 'Maintenance' AND
      EventStartTime >= '2019-08-17' AND
      EventStartTime < '2019-08-20'
GROUP BY TaskName ;

Note the date comparisons. First, the date values are in the format YYYY-MM-DD, which is the standard format for dates (in both SQL and ISO standards). Second, it uses >= and <. This is appropriate for both date and time comparisons and is safer than BETWEEN.

Upvotes: 0

Neeraj Agarwal
Neeraj Agarwal

Reputation: 1059

Please be aware that "08/17/2019" means "08/17/2019 00:00:00" and "08/19/2019" means "08/19/2019 00:00:00". You should use: EvenStartTime >= "08/17/2019" and EventStartTime < dateadd(dd, 1, "08/19/2019") to get all the matching records.

Upvotes: 0

Vidmantas Blazevicius
Vidmantas Blazevicius

Reputation: 4802

SUM is a function so you will need to use SUM(TaskLength) and then also GROUP BY other fields that you are selecting that are not part of your aggregate operation.

Try querying the below:

Query = 
@"SELECT TaskName, SUM(TaskLength)
 FROM myTable
 WHERE TaskName='Maintenance' AND EventStartTime
 BETWEEN '8/17/2019' AND '8/19/2019'
 GROUP BY TaskName"

Upvotes: 2

MPost
MPost

Reputation: 535

The summation is specified in the SUM() function in the SELECT clause, which requires a GROUP BY clause with all of the non-aggregated fields from the SELECT clause.

Query = @"SELECT TaskName, sum(TaskLength) as TotalTaskLength 
          FROM myTable
          WHERE TaskName='Maintenance' 
            AND EventStartTime BETWEEN '8/17/2019' AND '8/19/2019'
          GROUP BY TaskName"

Upvotes: 2

Related Questions