Get SUM of a field on a different table

[Erroneous]

Hello!

I am using SQL Server 2000. I have two tables. The first table is the list of employees (EMP_ID, firstname, lastname, etc). The second table is their production everyday( ID , WORDCOUNT, LINECOUNT, DURATION, etc)

wordcount and linecount are numbers while duration is hr:mm:ss

EMP_ID is the same with ID. Primary and foreign key.

I have a question. What is the proper SQL for getting the total sum of wordcount, linecount and duration from the other table.

I know the SUM() function of SQL and the Inner Join but i dont know how to combine them.

My desired output is the Employee's list (no duplicate) from table one plus the total sum of the three field from the other table.

TIA.

[si_the_geek]

The fact that there is a Join involved is actually irrelevant to what you want to achieve - it would be done the same way if there was a Join or not.

What you need is a Group By clause, which tells the database system to 'merge' rows with identical values for those fields. In this case you want it for the Employee fields, so would add all of those to the Group By, eg:

SELECT EMP_ID, firstname, lastname, ...
FROM ...
WHERE ...
GROUP BY EMP_ID, firstname, lastname, etc

You also need to tell it how to merge the values in the other fields, which you do with functions like Sum in the Select clause, eg:

SELECT EMP_ID, firstname, lastname, Sum(WORDCOUNT), Sum(LINECOUNT), Sum(DURATION)
FROM ...
WHERE ...
GROUP BY EMP_ID, firstname, lastname, etc

This needs to be done for every field in the Select clause that is not in the Group By clause.

[Erroneous]

Sorry i got confused. What is my final SQL statement?

[Erroneous]

Ok i tried the group by but it's not what i want to achieve. The first name and last name from the first table has duplicates.

My expected output should be one table with no duplicate of names but the other fields has summed up.

EMP_ID| Firstname | Lastname|Total SUM of Wordcount from 2nd table|Total SUM of Linecount from 2nd table|


My 2nd table looked like this

ID | Wordcount | Linecount |
---------------------------
1 |
2 |
1 |
1 |
3 |
3 |

The ID has duplicates here. I want to get the SUM as one and place it as a single field on the expected output.

[si_the_geek]

What is considered unique is the combination of values of all fields listed in the Group By clause. If you only want Firstname and Lastname to be used for that, only include those in the Group By clause (ie: remove Emp_ID).

If you don't want/need Emp_ID in your final output, simply remove it from the Select clause too. If you do need it, you will need to do something else (perhaps use a function, like Sum) to get the value you want from it.

[Blakk_Majik]

Please tell me "ID" is not a primary key.

[Erroneous]

What is considered unique is the combination of values of all fields listed in the Group By clause. If you only want Firstname and Lastname to be used for that, only include those in the Group By clause (ie: remove Emp_ID).

If you don't want/need Emp_ID in your final output, simply remove it from the Select clause too. If you do need it, you will need to do something else (perhaps use a function, like Sum) to get the value you want from it.

How about JOIN? I am using two tables. In your example i didn't see any JOIN syntax used.

[Erroneous]

Please tell me "ID" is not a primary key.

It is not a primary key coz it has duplicates. It is a foreign key to EMP_ID and EMP_ID is a primary key to table 1.

[si_the_geek]

Like I said before, the Join is irrelevant to this (it would be included in the FROM and/or WHERE clauses as you usually do it). I didn't show an example, as you said you know how to do that part.


If it still isn't giving the results you want, show us the current SQL statement, and some the data it returns (along with an explanation of why that data isn't what you want).

[Erroneous]

Thanks SI i got it sorted out. However i got one problem summing the duration coz the format is hh:mm:ss. It's returning an err.

Current SQL statement. Wordcount and linecount is summing up.

SELECT     TABLE1.EMP_ID, TABLE1.EMP_LASTNAME, TABLE1.EMP_FIRSTNAME, TABLE1.EMP_MIDDLE, 
                      SUM(TABLE2.WORDCOUNT) AS Expr1, SUM(TABLE2.LINECOUNT) AS Expr2
FROM         TABLE1 INNER JOIN
                      TABLE2 ON TABLE1.EMP_ID = TABLE2.ID
GROUP BY TABLE1.EMP_ID, TABLE1.EMP_LASTNAME, TABLE1.EMP_FIRSTNAME, TABLE1.EMP_MIDDLE

How do i sum up durations?

[si_the_geek]

Telling us that there was an error isn't much use by itself.. which error was it?

What is the data type of the field?

[Erroneous]

It is default as nvarchar. I honestly don't know what datatype to use. There's nothing much that fit into that category.

It cant be date/time coz its a duration.

[wild_bill]

What I would do is substring the data out, then convert it to its equivilent in seconds, so you can use the sum functionality, then you could convert it back if you wanted.

[si_the_geek]

A duration is a measure of time, and as such it could be stored as a Date/Time.

Doing that will mean that a Sum will work as expected, except in the case where the total goes beyond 24 hours, which would take a bit of effort to fix (perhaps as much as wild_bill's suggestion).


edit: An alternative, that is likely to be better, would be an extension of what wild_bill suggested - which would be to store it as a number of seconds (in a numeric data type). This would allow Sum, and would be relatively simple to convert to the format "hh:nn:ss".

[Erroneous]

Yeah that could be it. But I already started the database with 30k records ouch!

I can't redo this. I was hoping i could do it with pure SQL but it seems like it's either i hard code a loop or change my database.

Anyway, last question, is there any chance i could work around with pure SQL?

[si_the_geek]

You can do it with SQL, using SubString as wild_bill mentioned (to separate the string into the H/N/S parts), along with Cast to convert the strings to numeric, and then mathematical operators to get them to a total number of seconds.

That will end up looking something like this (untested):

Sum(
  Cast( SubString(Duration, 1, 2)  as Integer) * 3600 +
  Cast( SubString(Duration, 4, 2)  as Integer) * 60
  Cast( SubString(Duration, 7, 2)  as Integer)
)

..which will return the total number of seconds.


Converting it to the format you presumably want (HH:NN:SS) is easy enough, but it would be very long-winded to do it inline with the other work.

What I recommend is to create a function in the database to do it, something like this (again untested I'm afraid):

CREATE FUNCTION Seconds_to_HMS
(
  @NumberOfSeconds AS Integer
)
RETURNS VarChar
BEGIN 
  RETURN Cast(@NumberOfSeconds /3600 as VarChar) + ':' + Right('0' + Cast((@NumberOfSeconds  / 60) % 60 as VarChar), 2) + ':' + Right('0' + Cast(@NumberOfSeconds % 60 as VarChar), 2);
END

You can then add a call to that around the previous work, eg:

Seconds_to_HMS(
Sum(
  Cast( SubString(Duration, 1, 2) as Integer) * 3600 +
  Cast( SubString(Duration, 4, 2) as Integer) * 60
  Cast( SubString(Duration, 7, 2) as Integer)
)
)

[Erroneous]

Hey thanks Si. Let me try that. You are God send. + rep