12 Replies - 3597 Views - Last Post: 16 January 2013 - 06:03 AM

#1 Mohsin01  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 80
  • Joined: 13-April 12

Getting Sum of Column at the end of Select query.

Posted 15 January 2013 - 07:18 AM

Hello Guys,
I am trying to get sum of columns at the end of the query. i Mean all the data should be up there like select * from table... and at the end of each each column there should be sum of each column.i tried this but it didn't work for me.
  select total_Hours,duty_hours,overtime,salary,allowence
from (
  select EmployeeID as Total,  SUM(total_Hours) AS total_Hours,
             SUM(duty_hours) AS duty_hours,
             SUM(overtime) as Overtime,
             SUM(salary) AS salary,
             SUM(allowence) as Allowence
  from EmployeeHours
  WHERE EmployeeID = 4 
  GROUP BY EmployeeID 
) x
union all
select 'Total',SUM(total_Hours) as total_Hours,
			SUM(duty_hours) AS duty_hours,
			 
             SUM(overtime) as Overtime,
             SUM(salary) AS salary,
             SUM(allowence) as Allowence
  from EmployeeHours
  WHERE EmployeeID = 4 


and it gave me this error
Conversion failed when converting the varchar value 'Total' to data type int.


and the result is like sum of each column but i dont see values of culumns, i just see sum of coulmns.

I want something like this

ID   total_hrs Duty_hrs Ovtime salary  
4      10           8      2     100       
4      8            8      0     80        
4      12           8      4     120       
Total  30           24     6     300       



I tried much but i couldnt get result like this.
Please Help. THanks in advance.

This post has been edited by Mohsin01: 15 January 2013 - 07:23 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Getting Sum of Column at the end of Select query.

#2 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3611
  • View blog
  • Posts: 12,419
  • Joined: 12-December 12

Re: Getting Sum of Column at the end of Select query.

Posted 15 January 2013 - 08:02 AM

I think you need to remove the SUM()'s and GROUP BY from the first part. For example,

select product, quantity, something from tableName
union 
select 'Total', sum(quantity), sum(something) from tableName

Was This Post Helpful? 0
  • +
  • -

#3 Mohsin01  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 80
  • Joined: 13-April 12

Re: Getting Sum of Column at the end of Select query.

Posted 15 January 2013 - 09:41 AM

Its Not working either :rolleyes2:
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • It's just been revoked!
  • member icon

Reputation: 3611
  • View blog
  • Posts: 12,419
  • Joined: 12-December 12

Re: Getting Sum of Column at the end of Select query.

Posted 15 January 2013 - 09:47 AM

View PostMohsin01, on 15 January 2013 - 09:41 AM, said:

Its Not working either :rolleyes2:/>


Show the full SQL-statement as it is now.
Was This Post Helpful? 1
  • +
  • -

#5 Mohsin01  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 80
  • Joined: 13-April 12

Re: Getting Sum of Column at the end of Select query.

Posted 15 January 2013 - 10:40 AM

Its Already there Boss. Check my post. I posted whole statement and also posted what i wanted.
Was This Post Helpful? 0
  • +
  • -

#6 DarenR  Icon User is online

  • D.I.C Lover

Reputation: 484
  • View blog
  • Posts: 3,246
  • Joined: 12-January 10

Re: Getting Sum of Column at the end of Select query.

Posted 15 January 2013 - 11:01 AM

you cant sum alias columns:


take a look here to see what I am talking about.
Was This Post Helpful? 1
  • +
  • -

#7 Mohsin01  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 80
  • Joined: 13-April 12

Re: Getting Sum of Column at the end of Select query.

Posted 15 January 2013 - 11:40 AM

View PostDarenR, on 15 January 2013 - 11:01 AM, said:

you cant sum alias columns:


take a look here to see what I am talking about.

sorry to say but the link you gave me is not what i am asking. i am not adding two columns.
take a look at this thing again.
ID   total_hrs Duty_hrs Ovtime salary  
4      10           8      2     100       
4      8            8      0     80        
4      12           8      4     120       
Total  30           24     6     300 


Was This Post Helpful? 0
  • +
  • -

#8 DarenR  Icon User is online

  • D.I.C Lover

Reputation: 484
  • View blog
  • Posts: 3,246
  • Joined: 12-January 10

Re: Getting Sum of Column at the end of Select query.

Posted 15 January 2013 - 11:54 AM

   select total_Hours,duty_hours,overtime,salary,allowence  

02 from (  

03   select EmployeeID as Total,  SUM(total_Hours) AS total_Hours,  

04              SUM(duty_hours) AS duty_hours,  

05              SUM(overtime) as Overtime,  

06              SUM(salary) AS salary,  

07              SUM(allowence) as Allowence  

08   from EmployeeHours  

09   WHERE EmployeeID = 4   

10   GROUP BY EmployeeID   

11 ) x  

12 union all 

13 select 'Total',SUM(total_Hours) as total_Hours,  

14             SUM(duty_hours) AS duty_hours,  

15                 

16              SUM(overtime) as Overtime,  

17              SUM(salary) AS salary,  

18              SUM(allowence) as Allowence  

19   from EmployeeHours  

20   WHERE EmployeeID = 4 






try something like the following:


you may need to mess with it a little


   select EmployeeID, total_Hours,duty_hours,overtime,salary,allowence  

02 from 

08   from EmployeeHours  

09   WHERE EmployeeID = 4   

10   GROUP BY EmployeeID   


12 union all 

13 select EmployeeID,
SUM(total_Hours),  

SUM(duty_hours) ,  

SUM(overtime) ,  

SUM(salary) ,  

SUM(allowence) 

19   from EmployeeHours  

20   WHERE EmployeeID = 4 


This post has been edited by DarenR: 15 January 2013 - 11:55 AM

Was This Post Helpful? 1
  • +
  • -

#9 Mohsin01  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 80
  • Joined: 13-April 12

Re: Getting Sum of Column at the end of Select query.

Posted 15 January 2013 - 12:36 PM

View PostDarenR, on 15 January 2013 - 11:54 AM, said:

   select total_Hours,duty_hours,overtime,salary,allowence  

02 from (  

03   select EmployeeID as Total,  SUM(total_Hours) AS total_Hours,  

04              SUM(duty_hours) AS duty_hours,  

05              SUM(overtime) as Overtime,  

06              SUM(salary) AS salary,  

07              SUM(allowence) as Allowence  

08   from EmployeeHours  

09   WHERE EmployeeID = 4   

10   GROUP BY EmployeeID   

11 ) x  

12 union all 

13 select 'Total',SUM(total_Hours) as total_Hours,  

14             SUM(duty_hours) AS duty_hours,  

15                 

16              SUM(overtime) as Overtime,  

17              SUM(salary) AS salary,  

18              SUM(allowence) as Allowence  

19   from EmployeeHours  

20   WHERE EmployeeID = 4 






try something like the following:


you may need to mess with it a little


   select EmployeeID, total_Hours,duty_hours,overtime,salary,allowence  

02 from 

08   from EmployeeHours  

09   WHERE EmployeeID = 4   

10   GROUP BY EmployeeID   


12 union all 

13 select EmployeeID,
SUM(total_Hours),  

SUM(duty_hours) ,  

SUM(overtime) ,  

SUM(salary) ,  

SUM(allowence) 

19   from EmployeeHours  

20   WHERE EmployeeID = 4 



Error For second code is "Msg 8120, Level 16, State 1, Line 1
Column 'EmployeeHours.Duty_Hours' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause
."
Error for first is "Msg 205, Level 16, State 1, Line 1
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists."

Dont know, what to do now :(/>. Help !!!

This post has been edited by Mohsin01: 15 January 2013 - 12:37 PM

Was This Post Helpful? 0
  • +
  • -

#10 DarenR  Icon User is online

  • D.I.C Lover

Reputation: 484
  • View blog
  • Posts: 3,246
  • Joined: 12-January 10

Re: Getting Sum of Column at the end of Select query.

Posted 15 January 2013 - 01:40 PM

try this


 select EmployeeID, total_Hours,duty_hours,overtime,salary,allowence    

  

 from  

  from EmployeeHours    

   WHERE EmployeeID = 4     

     

 union all  

 select null,  

 SUM(total_Hours),    

 SUM(duty_hours) ,    

 SUM(overtime) ,    

 SUM(salary) ,    

 SUM(allowence)   

   from EmployeeHours    

   WHERE EmployeeID = 4 


Was This Post Helpful? 1
  • +
  • -

#11 Mohsin01  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 80
  • Joined: 13-April 12

Re: Getting Sum of Column at the end of Select query.

Posted 15 January 2013 - 03:02 PM

View PostDarenR, on 15 January 2013 - 01:40 PM, said:

try this


 select EmployeeID, total_Hours,duty_hours,overtime,salary,allowence    

  

 from  

  from EmployeeHours    

   WHERE EmployeeID = 4     

     

 union all  

 select null,  

 SUM(total_Hours),    

 SUM(duty_hours) ,    

 SUM(overtime) ,    

 SUM(salary) ,    

 SUM(allowence)   

   from EmployeeHours    

   WHERE EmployeeID = 4 



That one Finnaly Worked :bananaman: . Thank You Soooooooo Much Man :).

There a little bit problem if that can be solved. Its showing null and i want Total to be written there. I know that it will say that conversion failed due to string etc. but is there any way i can put total in place of null?
Was This Post Helpful? 0
  • +
  • -

#12 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Re: Getting Sum of Column at the end of Select query.

Posted 16 January 2013 - 05:19 AM

SELECT Cast(EmployeeID AS VARCHAR(6)) AS EmployeeID, total_Hours, duty_hours, overtime, salary, allowence
FROM   FROM EmployeeHours
WHERE  EmployeeID = 4     
UNION ALL  
SELECT 'Total', SUM (total_Hours), SUM (duty_hours), SUM (overtime), SUM (salary), SUM (allowence)
FROM   EmployeeHours
WHERE  EmployeeID = 4 


i can say this that the above query is solve your problem
but

you need to take a look at this if you use sqlserver 2005 and above

Roll Up introduction

Summarize Results using Roll up
Was This Post Helpful? 1
  • +
  • -

#13 Mohsin01  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 80
  • Joined: 13-April 12

Re: Getting Sum of Column at the end of Select query.

Posted 16 January 2013 - 06:03 AM

View Postthava, on 16 January 2013 - 05:19 AM, said:

SELECT Cast(EmployeeID AS VARCHAR(6)) AS EmployeeID, total_Hours, duty_hours, overtime, salary, allowence
FROM   FROM EmployeeHours
WHERE  EmployeeID = 4     
UNION ALL  
SELECT 'Total', SUM (total_Hours), SUM (duty_hours), SUM (overtime), SUM (salary), SUM (allowence)
FROM   EmployeeHours
WHERE  EmployeeID = 4 


i can say this that the above query is solve your problem
but

you need to take a look at this if you use sqlserver 2005 and above

Roll Up introduction

Summarize Results using Roll up


Finally I got what i wanted excatly :bananaman:. Thanks THava :bigsmile: :bigsmile: .
P.s:Could anyone tell me where i can find best toturial to learn crsytal reporting with C# sql server.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1