Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-23 : 08:57:47
|
Tony writes "I have the setup below in an SQL server and I am trying to calculate the subtotal of the records based on the HH_SSN. You can see that the MBR_SSN is dependent on the HH_SSN.HH_SSN MBR_SSN INCOME1 INCOME2 INCOME3 INCOME4 INCOME5 2786 2786 243 0 65 0 0 2786 2786 469 485 0 0 0 2786 5637 593 0 0 0 0 2786 5637 13 13 0 0 0 2786 5659 13 13 530 0 0 2786 6254 13 13 522.67 0 0 2786 6186 13 13 503.42 0 0 This is exactly how the data looks like in the table. The situation here is that the MBR_SSN depends on the HH_SSN for housing and so they live under one roof. I have to generate a calculation for the subtotal in SQL, I have successfully done the Row calculation, but I am having problems with the subtotal of income 1,2,3,4 and 5. That is the total for income1,income2 and so on.I have 1000 records plus with this sort of arrangement in the table. Below is the SQL is used for the Row calculation: Total_Income_Undereported AS (INCOME1)- (INCOME2 + INCOME3 + INCOME4 + INCOME5)Thanks for your help. " |
|
|
leeholden
Starting Member
34 Posts |
Posted - 2002-04-23 : 10:40:47
|
| You need Total_Income_Unreported in your output so that you can use COMPUTE:select hh_ssn, I1-(I2+I3+I4+I5) as Total_Incomefrom Income_Tableorder by hh_ssncompute sum(I1-(I2+I3+I4+I5)) by hh_ssn--------------------------------------------------Ban all NULLs and employ Cowboy Coders |
 |
|
|
|
|
|
Calculating columns as subtotal