Click here to Skip to main content
15,851,387 members
Please Sign up or sign in to vote.
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
Updated 22-Sep-14 18:33pm
v3
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

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
 
Share this answer
 
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.
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
 
Share this answer
 
Comments
Denrich 22-Sep-14 23:30pm    
Already test this one, but it said that the CUBE is not recognized
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%')
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900