| Author |
Topic |
|
besadmin
Posting Yak Master
116 Posts |
 Posted - 2008-06-02 : 15:26:36
|
| Hey Everyone!I was wondering if i could please get some help on creating total columns and rows. i want to total some items for each customer in a row, and have a total column for each customer. i also want to total each type at the bottom so i have a total row for each type.finally, how do i create a column that finds a percentage of a certain type for each customer out of the total types for each customer, creating a %of type from total column.i really appreciate any help and replies to this issue. this is business not personal, so i am very greatful of any help.Thanks again!-S |
|
|
raja_saminathan
Starting Member
12 Posts |
Posted - 2008-06-03 : 04:46:39
|
| Hi -s ,can you provide information about the table Schema ,sample Datas and your expected reslts on those datas when queried.Rajesh |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-03 : 05:02:53
|
quote:
Originally posted by besadmin
Hey Everyone!I was wondering if i could please get some help on creating total columns and rows. i want to total some items for each customer in a row, and have a total column for each customer. i also want to total each type at the bottom so i have a total row for each type.finally, how do i create a column that finds a percentage of a certain type for each customer out of the total types for each customer, creating a %of type from total column.i really appreciate any help and replies to this issue. this is business not personal, so i am very greatful of any help.Thanks again!-S
Is this for a report. If yes its very easier to calculate the percentage and totals at front end. |
|
|
|
besadmin
Posting Yak Master
116 Posts |
Posted - 2008-06-03 : 09:29:20
|
| Sorry...I should have given more info before...here you go. Thanks again for any help you could provide!i have columns titled: NAME - FAX - INTERNET - PHONE - OTHERSo i want to make a new column at the end so it shows the total of all type for each customer. example:columns titled: NAME - FAX - INTERNET - PHONE - OTHER - TOTALso it adds fax, internet, phone, other...Then, for the very last row in the table i want it to be likeTotal FAX - INTERNET - PHONE - OTHER - TOTAL 23 45 12 98 687Just like that.sorry if this is confusing, if more clarification is needed just ask.I am looking forward to any replys!Thanks again!-S |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-06-03 : 10:33:30
|
| The total is derived data. You should not store this data in the table. You should calculate this in a view or stored procedure, or you should let your reporting application add the values.e4 d5 xd5 Nf6 |
|
|
|
besadmin
Posting Yak Master
116 Posts |
Posted - 2008-06-03 : 11:48:37
|
| oh, thanks blindman.If i was going to put it in with that table though...how would i do it?Im just curious what the code would be or look like when doing that?thanks a ton!-s |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-06-03 : 12:33:01
|
| The problem is that you would need the total to be updated every time one of the parameter columns is changed. To do this, you would need to implement an INSERT/UPDATE trigger on the table.e4 d5 xd5 Nf6 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-03 : 13:56:48
|
quote:
Originally posted by besadmin
oh, thanks blindman.If i was going to put it in with that table though...how would i do it?Im just curious what the code would be or look like when doing that?thanks a ton!-s
Use a computed column for total columnCREATE TABLE mytable ( NAME varchar(100), FAX int,INTERNET int, PHONE int, OTHER int, TOTAL AS FAX + INTERNET + PHONE + OTHER ) |
|
|
|
besadmin
Posting Yak Master
116 Posts |
Posted - 2008-06-03 : 16:22:44
|
| Thanks for reply...that didnt work though.I really would like to know how to add the values in a rowslike row = name fax internet otherso the row looks like bill, 20, 5, 2so i want it to say row = name fax internet other totalbill, 20, 5, 2, 27i hope that makes sense!pleasee help me...this is for a managers report at work!thanks soo much! |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-06-03 : 21:39:14
|
| Visakh code will do EXACTLY what you describe.Now don't post again until you have tried it.e4 d5 xd5 Nf6 |
|
|
|
besadmin
Posting Yak Master
116 Posts |
Posted - 2008-06-04 : 00:16:49
|
| Sorry blindman...i am pretty new to SQL, trying to learn as much as i can. i enjoy it, but it is also becoming a very important part of my job in IT.So..it is most definatley possible that i did it wrong.. i used visakh code...which i very much appreciate, but all i got were column headings with those names. like the, fax + net + phone gave me column headings. if you understand why that is, visakh i would love some clarification on what i might have done wrong.in the meantime blindman...dont ever accuse me for saying someones code didnt give me the result i wanted....and say not to post again until i tried it.OF COURSE i tried before i posted again for further info.A little more respect, and a little less accusation never hurt anyone.Thanks to everyone who has been a help so far!!Keep up the good work! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-04 : 00:54:11
|
quote:
Originally posted by besadmin
Sorry blindman...i am pretty new to SQL, trying to learn as much as i can. i enjoy it, but it is also becoming a very important part of my job in IT.So..it is most definatley possible that i did it wrong.. i used visakh code...which i very much appreciate, but all i got were column headings with those names. like the, fax + net + phone gave me column headings. if you understand why that is, visakh i would love some clarification on what i might have done wrong.in the meantime blindman...dont ever accuse me for saying someones code didnt give me the result i wanted....and say not to post again until i tried it.OF COURSE i tried before i posted again for further info.A little more respect, and a little less accusation never hurt anyone.Thanks to everyone who has been a help so far!!Keep up the good work!
Didnt understand what you're speaking about. Did you try populating the table with some data and retrieving them using SELECT fields from tabkename? What i've given is only the table creation script with computed column definition. When you try to retrieve the data from your table, it will calculate the value of total automatically based on our definition and provide you with the result.Try populating with some data and retrieving.Blindman was not accusing you. He was just asking you to have a try before you repost. Most posters here are interested only in getting a working solution without understanding what they meant or how they work. Thats why we keep on insisting the posters to try out themselves and understand the solution given before asking again.Please keep in mind that we are here to help you out and make you understand why a particular problem occured rather than accusing you or mocking you on it. I'm sure Blindman meant no offences while asking you to try out the solution.  |
|
|
|
besadmin
Posting Yak Master
116 Posts |
Posted - 2008-06-04 : 11:33:08
|
| thanks v. well i am not a user looking for a free solution. i would love to understand. i have associates degree in IT. Going for BS degree. MY specialization is Computer Programming and Databases. Just have noo sql practice or classes yet. so it is definatley important for me to try and not only learn, but understand. i will work more with what you have me and see if i can figure it out. i just want it to be known that i wont look at something and naa that wont work without certainly trying it first...but, i am sorry if i overreacted to you blindman.i will continue working on it V and get back to you if i still cant figure it out. your help is very much appreciated!talk to you soon!layte |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-04 : 13:27:23
|
quote:
Originally posted by besadmin
thanks v. well i am not a user looking for a free solution. i would love to understand. i have associates degree in IT. Going for BS degree. MY specialization is Computer Programming and Databases. Just have noo sql practice or classes yet. so it is definatley important for me to try and not only learn, but understand. i will work more with what you have me and see if i can figure it out. i just want it to be known that i wont look at something and naa that wont work without certainly trying it first...but, i am sorry if i overreacted to you blindman.i will continue working on it V and get back to you if i still cant figure it out. your help is very much appreciated!talk to you soon!layte
No worries You're always welcome |
|
|
|
|
Creating Total Columns and Rows