sql query help

Question

Hi all,

I had a request to show total services being run etc. but am struggling with the output.

UserName Services1 Services2 Minutes    FullService
JIM         5          1     0.348837       70
JIM         5          1     0.837209       91
JIM         5          1     1.302325      111
JIM         5          1     1.813953      133
JIM         5          3     1.395348    115

What I want to see is

UserName    Services1 Services2 Minutes    FullService
JIM             25        7     5.697672   30.697672

What I am getting is

UserName    Services1 Services2 Minutes    FullService
JIM               27      7     5.697674   520

For some reason it is adding an extra 2 services1, and only summing the full service column, instead of adding services1 + Minutes together... Any ideas would be greatly appreciated... Below is my sql the adding columns bit doesn't seem to work...

SELECT  US.UserName ,
        SUM(COALESCE(Services1, 0)) / 10 AS [Services1] ,
        SUM(COALESCE(Services2 , 0)) AS [Services2] ,
        SUM(Minutes) / 43 AS Minutes ,
        (CONVERT(INT,((Services1) + (Minutes)))) AS FullService

Answer 1

0

The problem was how I was referencing columns in my select statement. I wasn't doing calculations on (CONVERT(INT,(SUM((Services1) + (Services2)))) AS FullService when what I should have done was

CONVERT(INT,(SUM((COALESCE(Services1, 0) / 10)+(Services2/ 43))))!!!

Anyone reading this remember the order of how SQL constructs it's queries!

answered Feb 13 '12 at 04:45 PM

jhowe
919 ● 29 ● 42 ● 46

What I could understand from snippet is that you may use isnull instead of coalesce function

Feb 13 '12 at 04:53 PM inder

(comments are locked)

Answer 2

0

If you do not let us know the details, how could you expect a perfect answer? But seems like you need to change the

(CONVERT(INT,((Services1) + (Minutes)))) AS FullService

to

(CONVERT(INT, SUM(Services1 + Minutes))) AS FullService --CONVERSION AFTER THE SUM OPERATION

answered Feb 13 '12 at 01:21 PM

robbin
1.6k ● 1 ● 3 ● 5

(comments are locked)

Answer 3

You changed the question after I posted by comment above, so here is the answer now with the column defined properly:

DECLARE @TestTable AS TABLE
    (UserName varchar(20),
     Services1 smallint,
     Services2 smallint,
     [Minutes] decimal(10, 6),
     FullService smallint)

INSERT  INTO @TestTable
VALUES  ('JIM', 5, 1, 0.348837, 70),
        ('JIM', 5, 1, 0.837209, 91),
        ('JIM', 5, 1, 1.302325, 111),
        ('JIM', 5, 1, 1.813953, 133),
        ('JIM', 5, 3, 1.395348, 115)

SELECT  UserName,
        SUM(Services1) AS [Services1],
        SUM(Services2) AS [Services2],
        SUM(Minutes) AS [Minutes],
        SUM(Services1 + [Minutes]) AS FullService
FROM @TestTable
GROUP BY UserName

Just as @robbin wrote, run the SUM() around the addition and you get the expected output.

sql query help

Follow this question

Related Questions