How to get the sum of a column using alias in SQL query

Question

0.00/5 (No votes)

See more:

i want to get the sum of the alias sub_total.

here's my query

SQL

SELECT SUM(Project_Total) AS Sub_Total, Project_Code, SectionCode
FROM Temp_Portal
WHERE (Project_Code = ?)
GROUP BY Project_Code, SectionCode
HAVING (Project_Code LIKE 'BA%') AND (SectionCode LIKE 'RN%')

here is the result of the query above

Project_Code    SectionCode    Sub_Total
BA82014       |       RN1     |   86
BA82014       |       RN      |  18372

The result i need is this one

XML

<pre>
    Project_Code    SectionCode    Sub_Total
    BA82014       |       RN1     |   86
    BA82014       |       RN      |  18372   
                           Total  :  18458 </pre>

I need to get the total sum of rn and rn1.
somebody know how to do this?

Thanks in advance!

Posted 17-Sep-14 23:01pm

Denrich

Updated 22-Sep-14 17:33pm

v3

Add a Solution

Comments

Animesh Datta 18-Sep-14 5:20am

what your expected output ?

Denrich 22-Sep-14 21:55pm

Project_Code SectionCode Sub_Total
BA82014 | RN1 | 86
BA82014 | RN | 18372
Total | 18458

I want to get the total of RN1 and RN

3 solutions

Solution 2

Using the WITH CUBE clause at the end of your query will give you an additional row at the end of your result with the sum of the sub_total and all other column values will be null. So in one query you can get everything

SQL

SELECT SUM(Project_Total) AS Sub_Total, Project_Code, SectionCode
FROM Temp_Portal
WHERE (Project_Code = ?)
GROUP BY Project_Code, SectionCode
HAVING (Project_Code LIKE 'BA%') AND (SectionCode LIKE 'RN%')
WITH CUBE

Posted 18-Sep-14 0:48am

Gauri Chodanker

Comments

Denrich 22-Sep-14 23:30pm

Already test this one, but it said that the CUBE is not recognized

Solution 3

SQL

SELECT SUM(Project_Total) AS Sub_Total, Project_Code, SectionCode
FROM Temp_Portal
WHERE (Project_Code = ?)
GROUP BY Cube(Project_Code, SectionCode)
HAVING (Project_Code LIKE 'BA%') AND (SectionCode LIKE 'RN%')

Posted 18-Sep-14 0:58am

Samatha Reddy G

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)

Magic Wonder · Accepted Answer · 2014-09-17T23:30:00

Solution 1

Hi,

Check this...

SQL

SELECT SUM(X.Sub_Total)
FROM
(
SELECT SUM(Project_Total) AS Sub_Total, Project_Code, SectionCode
FROM Temp_Portal
WHERE (Project_Code = '?' )
GROUP BY Project_Code, SectionCode
HAVING (Project_Code LIKE 'BA%') AND (SectionCode LIKE 'RN%')
) X

Hope this will help you.

Cheers

Posted 17-Sep-14 23:30pm

Magic Wonder

Updated 22-Sep-14 20:18pm

v2

Comments

Denrich 22-Sep-14 23:29pm

i tried this, but i only get an error :(

Magic Wonder 23-Sep-14 1:52am

What is the error you are getting?

Denrich 23-Sep-14 1:56am

syntax error or access violation.
i just copied this code, is there anything i need to change?

Denrich 23-Sep-14 1:56am

thanks for your reply, i hope you can help me with my problem

Magic Wonder 23-Sep-14 2:18am

I am also hoping the same....anyway hope for best.

Magic Wonder 23-Sep-14 2:17am

There might be error in ?...i have changed it to '?'...check above script.

Denrich 23-Sep-14 2:24am

I tried it, but it only give a blank result :(

Denrich 23-Sep-14 2:33am

hi sir :) i made it! yehey! thank you for the help! :)

Magic Wonder 23-Sep-14 2:36am

Your welcome.