asked
Select with sum from one table, Select single field from another
DECLARE @Totaltbl TABLE ( Job_Number numeric (6,0), Period_Year numeric (4,0), Period_Number numeric (2,0), TTLPerRev decimal (11,2), TTLYTDRev decimal (11,2), TTLJTDRev decimal (11,2), TTLPerRABU decimal (11,2), TTLYTDRABU decimal (11,2), TTLJTDRABU decimal (11,2),TTLPerRevAj decimal (11,2), TTLYTDRevAj decimal (11,2), TTLJTDRevAj decimal (11,2) ) INSERT INTO @Totaltbl (Job_Number, Period_Year, Period_Number, TTLPerRev, TTLYTDRev, TTLJTDRev) SELECT TTLPerRev=( Table1.col1 + Table1.col2 + Table1.col3), TTLYTDRev=(Table1.col4 + Table1.col5 + Table1.col6), TTLJTDRev=(Table1.col7 + Table1.col8 + Table1.col9) FROM dbo.Table1 Where (Table1.Job_Number = @Job_Number) and (Table1.Period_Year = @Period_Year) and (Table1.Period_Number = @Period_Number) UPDATE @Totaltbl SET Job_Number = Job_Number, Period_Year = Period_Year, Period_Number = Period_Number, TTLPerRev = TTLPerRev, TTLYTDRev = TTLYTDRev, TTLJTDRev = TTLJTDRev INSERT INTO @Totaltbl (TTLPerRABU, TTLYTDRABU, TTLJTDRABU) SELECT TTLPerRABU = Table1.col_10, TTLYTDRABU = Table1.col_11, TTLJTDRABU = Table1.col_12 FROM dbo.Table1 Where (Table1.Job_Number = @Job_Number) and (Table1.Period_Year = @Period_Year) and (Table1.Period_Number = @Period_Number) UPDATE @Totaltbl SET TTLPerRABU = TTLPerRABU, TTLYTDRABU = TTLYTDRABU, TTLJTDRABU = TTLJTDRABU INSERT INTO @Totaltbl (TTLPerRevAj, TTLYTDRevAj, TTLJTDRevAj) SELECT TTLPerRevAj = SUM(Table2.Col1), TTLYTDRevAj = SUM(Table2.Col2), TTLJTDRevAj = SUM(Table2.Col3) Where (Table2.Job_Number = @Job_Number) and (Table2.Period_Year = @Period_Year) and (Table2.Period_Number = @Period_Number) Group By Table2.Job_Number, Table2.Financial_Period_Year, Table2.Period_Number UPDATE @Totaltbl SET TTLPerRevAj = TTLPerRevAj , TTLYTDRevAj = TTLYTDRevAj, TTLJTDRevAj = TTLJTDRevAj SELECT Job_Number, Period_Year, Period_Number, TTLPerRev, TTLYTDRev, TTLJTDRev, TTLPerRABU, TTLYTDRABU, TTLJTDRABU, TTLPerRevAj, TTLYTDRevAj, TTLJTDRevAj FROM @Totaltbl
@technette If every insert you have inserts just one row then you can restate the code in your question like this to get one record, 12 columns you need:
;with records (Job_Number, Period_Year, Period_Number,
TTLPerRev, TTLYTDRev, TTLJTDRev,
TTLPerRABU, TTLYTDRABU,TTLJTDRABU) as
(
select
Job_Number, Period_Year, Period_Number,
TTLPerRev = Col1 + Col2 + Col3,
TTLYTDRev = Col4 + Col5 + Col6,
TTLJTDRev = Col7 + Col8 + Col9,
TTLPerRABU = Col10,
TTLYTDRABU = Col11,
TTLJTDRABU = Col12
from Table1
where
Job_Number = @Job_Number and
Period_Year = @Period_Year and
Period_Number = @Period_Number
)
select
records.*, c.TTLPerRevAj, c.TTLYTDRevAj, c.TTLJTDRevAj
from records cross apply
(
select
TTLPerRevAj = sum(Col1),
TTLYTDRevAj = sum(Col2),
TTLJTDRevAj = sum(Col3)
from Table2
) c; I don't see the need to involve the table variable here, but if you need it then you can add the line **insert into** just before the final select.
If you're OK with grouping on all Table2 columns, you should add GROUP BY Table2.col1, Table2.col2, Table2.col3, Table2.col4 If that's not what you want, please give some output examples of what you want to achieve.
Magnus, Thank you for responding. Unfortunately, I don't have an example of the output. I am able to get the data I need from the tables separately but I also need to produce a total of combined totals. From Table2, I will have a total of Table2.col1A, Table2.col2A, Table2.Col3A, Table2.col1B, Table2.col2B, Table2.Col3B, Table2.col1C, Table2.col2C, Table2.Col3C From Table2, sum(col1), sum(col2), sum(col3) I have to get the total of the columns and add to the sums respectively. Example: Table2.col1A + Table2.col1B + Table2.col1C + sum(col1)
Use MyDatabase; Go If Object_ID ('dbo.RevTotals', 'U') IS NOT NULL DROP TABLE dbo.RevTotals; Go AlTER DATABASE ESTFLS SET RECOVERY BULK_LOGGED; Go SELECT INTO dbo.RevTotals From Table2 subTTR1=(Table2.Col1+ Table2.Col2+ Table2.Col3), subTTR2=(Table2.Col4 + Table2.Col5+ Table2.Col6), subTTR3=(Table2.Col7+ Table2.Col8+ Table2.Col9). --Additional data from the same table I need: SELECT INTO dbo.RevTotals From Table2 Table2.Col_10, Table2.Col_11, Table2.Col_12 SELECT INTO dbo.RevTotals From Table1 sum1 = sum(Table1.Col1), sum2 = sum(Table1.Col2), sum3 = sum(Table1.Col3) Return Don't know how I will get me total colums from here.
I have to produce fields for my report. The data for the individual select statements appear in different sections of the report. In reports, you can't total the amounts from different sections. This stored procedure will allow me to create individual fields that I can add to the statistical part of the report, and create calculated fields. I'm almost there. just need to return one row of the selected regions so that I can calculate accordingly on the report.
Maybe I am wrong in trying to understand the requirements, but it appears that there is a table named table2 with 9 columns and the expected results consists of 3 columns each combining 3 columns of table2 and as many records as there are records in table2. There is also another table named table1, which has 3 columns, from which the sum of each is needed to be also included in the final result. Something like this: if object_id('dbo.RevTotals', 'U') is not null drop table dbo.RevTotals; ;with records (Col1, Col2, Col3) as ( select Table2.Col1+ Table2.Col2+ Table2.Col3 Col1, Table2.Col4+ Table2.Col5+ Table2.Col6 Col2, Table2.Col7+ Table2.Col8+ Table2.Col9 Col3 from Table2 union all select sum(Table1.Col1), sum(Table1.Col2), sum(Table1.Col3) from Table1 ) select * into dbo.RevTotals -- don't know why, just select would do from records; go The cross join in the question definition does not appear to be a correct choice as it causes a cartesian product of 2 tables to return and I am not sure whether this is desired. <\!-- Begin Edit It is still difficult to figure out the requirements. Could you please let me know how many records would you like your procedure to return? From the script in your question, it appears that you want millions of records from Table2 (9 columns are bunched into 3 with Col2 + Co2 + Col3 = SomeColumn etc). You also want millions of records from the same Table2 using Col10 for first column, Col11 for second and Col12 for third. Then you want just one record from Table1 (3 columns displaying summary of each). I will assume that this assumption is wrong and what is actually needed is this: - One record from Table2 displaying 3 columns on the basis of sum(Col1 + Col2 + Col3) e.t.c - One record from Table2 displaying 3 columns on the basis of sum(Col10) e.t.c. - One record from Table1 displaying 3 columns on the basis of sum(Col1) e.t.c. If this is the case then you can use something like this (I removed creation of the table part because I cannot figure out its purpose) ;with records (RecordID, Col1, Col2, Col3) as ( -- this is the 9 columns from table2 part select 1 RecordID, sum(Col1 + Col2 + Col3) Col1, sum(Col4 + Col5 + Col6) Col2, sum(Col7 + Col8 + Col9) Col3 from Table2 union all -- this is the cols 10 to 12 from table2 part select 2, sum(Col10), sum(Col11), sum(Col12) from Table2 union all -- this is the cols 1 to 3 from table1 part select 3, sum(Col1), sum(Col2), sum(Col3) from Table1 ) select sum(Col1) Col1, sum(Col2) Col2, sum(Col3) Col3 from records -- fake group by so grand totalling works group by RecordID with rollup; go The query above should return result in 4 records and 3 columns with last record of the result displaying the grand total for each returned column. End Edit --> Oleg
Oleg, Almost.. 1. Yes Table 2 has 9 columns, three sets of columns each set will produce a total. (three subtotals) 2. Table 2 has 3 other columns that must be included in the total 3. Table 1 has three sum total columns The final result would be grandtotalling three columns. I need to be able to insert the three grandtotals into a report. Table 1 and table 2 have three parameters
@technette Please let me know about the details by stating some sample number of records, i.e. Table2 has 100 records and 12 columns, and I need 100 records from it (or whatever that number is). Table 2 has 50 records but I only need one record as the output... Somehow, I cannot figure out the requirements, it has been a long day.
Oleg, I'm sorry that I haven't clearly stated the requirement. Table1 Is a view, Financial that has million of rows of transactions. by Jobs, PeriodYear and Month. I need to subtotal three colums and display them in my report. Table2 is a view, Financial that has million of rows of financial data. I have to select 9 columns. These will create three colums that need to be totaled. The three totals will appear on the report. Table2 has three other columns that are displayed on the report in another place. I will have to display the grand total of the three columns on a report.
Oleg, It looks like this may work. I'll get back on this. You are correctly stating that I will be displaying one record from each set. The views have millions of rows and the user will provide input to three parameters. Job, Year, Month. These are common parameters in each of the views.
