x

sql query help

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
more ▼

asked Feb 13, 2012 at 12:16 PM in Default

jhowe gravatar image

jhowe
1.1k 49 57 60

Seems like it is not the full script you have posted? Is the information derived from only one table?
Feb 13, 2012 at 12:32 PM robbin

Going with the information you provided you already get what you expect:

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 
        --(CONVERT(INT,((HubTotalServices) + (NonHubMinutes)))) AS FullService
FROM @TestTable
GROUP BY UserName

I have no idea how you make the final column (FullService) as you are referencing other tables/columns not mentioned in your question. PLease provide a little more detail so we can help out further.

Test data, table definitions and expected result usually help us out the most.
Feb 13, 2012 at 12:36 PM WilliamD
it's the OUTPUT i'm concerned with as I only want to show one row instead of multiple rows... as mentioned earlier --(CONVERT(INT,((Services1) + (Minutes)))) AS FullService is not giving me the results I want. The columns are not adding together correctly and also if I include them in my query I have to add them into the group by which splits the rows out...
Feb 13, 2012 at 12:55 PM jhowe
I haven't added tables etc. cause it's a fairly lengthy/complicated query and think that I will be able to resolve it once I figure out the full service calculation and stop the output from displaying multiple rows...
Feb 13, 2012 at 12:56 PM jhowe
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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!

more ▼

answered Feb 13, 2012 at 04:45 PM

jhowe gravatar image

jhowe
1.1k 49 57 60

What I could understand from snippet is that you may use isnull instead of coalesce function
Feb 13, 2012 at 04:53 PM inder
(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Feb 13, 2012 at 01:21 PM

robbin gravatar image

robbin
1.6k 1 3 5

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Feb 13, 2012 at 02:10 PM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

I found the problem... it was a problem with my subqueries... Doh! Thanks for your help though!
Feb 13, 2012 at 04:36 PM jhowe
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1833
x579

asked: Feb 13, 2012 at 12:16 PM

Seen: 659 times

Last Updated: Feb 14, 2012 at 12:50 PM