How to use alias and sum them together in select query

Question

0.00/5 (No votes)

See more:

I have the following queries below. I'd like to use the aliases and add them together but SQL does not allow that. Any suggestions on how I can go about this without repeating the queries again? Using Microsoft SQL Server 2008

C#

SqlCommand command = new SqlCommand(@"SELECT Workers.Full_Name,Workers.Salary, 
 CASE WHEN (SUM(JobHours.Additional_Time)) > 0 THEN (SUM(JobHours.Additional_Time)) ELSE (0) END AS Plusovertime,
            CASE WHEN (SUM(JobHours.Additional_Time)) > 0 THEN ROUND(((Workers.Salary/300)*1.5),0) ELSE (0) END AS PlusRental,             
(Plusovertime+PlusRental) AS PULSNET

 FROM Workers INNER JOIN JobHours ON Workers.Worker_ID = JobHours.Worker_ID 

 WHERE  Jobhours.Start_Work BETWEEN @startdatetime AND @enddatetime
            GROUP BY Workers.Worker_ID, Workers.Full_Name,Workers.Salary
            ORDER BY Workers.Worker_ID;", conn);

Posted 15-Oct-14 23:44pm

firaso2014

Updated 15-Oct-14 23:51pm

Herman<T>.Instance

v2

Add a Solution

Comments

KaushalJB 16-Oct-14 6:02am

You mean to say that you want SqlCommand commandFinal = command1 + command2 + command3 ? Is this you need ?

firaso2014 16-Oct-14 6:43am

no
I want to use alias in case1 with alias in case2 and sum them without repeat whole case
must I use subquery?
how to use it?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-10-16T02:57:00

Solution 2

You can achieve that by using subquery:

SQL

SELECT Sum1, Sum2, Sum1+Sum2 AS Sum3
FROM (
    SELECT SUM(Field1) AS Sum1, SUM(Field2) AS Sum2
    FROM TableName
    GROUP BY ...
) AS T

Posted 16-Oct-14 2:57am

Maciej Los

Ainy Mughal · Answer 2 · 2014-10-16T01:42:00

Solution 1

http://stackoverflow.com/questions/3932205/to-calculate-sum-two-alias-named-columns-in-sql[^]

Posted 16-Oct-14 1:42am

Ainy Mughal