CODEWITHSUNDEEP
sqlms-accesssum
Migos
Migos

Reputation: 148

SQL - How to SUM columns from 3 other Queries

I want to total 3 columns in 3 Tables generated from Queries. The three tables are Payroll_Table, Misc_Table, Depreciation_Table. Each table has Store_ID and Payroll_Expense or Misc_Expense or Depreciation_Expense columns.

I am trying to obtain the total of Payroll, Misc, and Depreciation for each Store_ID.

SELECT Store_ID, SUM(Payroll_Expense + Misc_Expense + Depreciation_Expense) AS Total
FROM (
SELECT Store_ID, Payroll_Expense FROM [Payroll_Table]UNION ALL
SELECT Store_ID, Misc_Expense AS FROM [Misc_Table] UNION ALL
SELECT Store_ID, Depreciation_Expense FROM [Depreciation_Table]
)
GROUP BY Store_ID

If I run this query, I get a prompt to 'Enter Parameter Value' for Misc_Expense and Depreciation_Expense. If I enter 1, I get the correct value but I am unsure why I get this prompt.

Upvotes: 0

Views: 45

Answers (2)

xQbert
xQbert

Reputation: 35333

So the union is generating only two columns store_ID and "Expense" (Actually Payroll_Expense)

The system doesn't know what misc_expense and depreciation_expense are; which is why you get prompted for both. This can be seen if you do a select * from your derived table as follows:

SELECT * 
FROM (
SELECT Store_ID, Payroll_Expense Expense FROM [Payroll_Table]UNION ALL
SELECT Store_ID, Misc_Expense FROM [Misc_Table] UNION ALL
SELECT Store_ID, Depreciation_Expense FROM [Depreciation_Table]
) DerivedTable

So based on this you can see there isn't a MISC_EXPENSE column and a Depreciation_Expense; they are all rolled into Payroll_Expense (Expense). So the derived table has only 2 columns, not the 4 you were expecting by your outer select. We could still get 4; you just have to use option 2 below; but is it needed?

To resolve: Simply omit Misc_Expense and Depreciation_Expense in your outer query as they are in the "Expense" column as a result of the and alias the column for posterity.

The column names and data types for each column in a union are inherited from the column names and data types of the first query; unless, null, then it's the first data type encountered.

So we can just do this:

SELECT Store_ID, SUM(Expense) AS Total
FROM (
SELECT Store_ID, Payroll_Expense AS Expense FROM [Payroll_Table]UNION ALL
SELECT Store_ID, Misc_Expense FROM [Misc_Table] UNION ALL
SELECT Store_ID, Depreciation_Expense FROM [Depreciation_Table]
) as DerivedTable
GROUP BY Store_ID

If for some reason you still need all 3 columns then you need placeholders for the other two columns. (OPTION 2)

SELECT Store_ID, SUM(Payroll_Expense + Misc_Expense + Depreciation_Expense) AS Total
FROM (
SELECT Store_ID
     , Payroll_Expense
     , NULL as Misc_expense
     , Null as Depreciation_expense 
FROM [Payroll_Table] UNION ALL
SELECT Store_ID
     , NULL as Payroll_Expense
     , Misc_Expense
     , Null as Depreciation_expense
FROM [Misc_Table] UNION ALL
SELECT Store_ID
     , Null as Payroll_expense
     , Null as Misc_expense
     , Depreciation_Expense 
FROM [Depreciation_Table]
) as DerivedTable
GROUP BY Store_ID

Upvotes: 0

T McKeown
T McKeown

Reputation: 12847

You can't refer to different column names when you UNION you should refer to the index/position of the column or alias the columns like this:

SELECT Store_ID, SUM(Expense) AS Total
FROM (
SELECT Store_ID, Payroll_Expense AS [Expense] FROM [Payroll_Table]UNION ALL
SELECT Store_ID, Misc_Expense AS [Expense] FROM [Misc_Table] UNION ALL
SELECT Store_ID, Depreciation_Expense AS [Expense] FROM [Depreciation_Table]
)
GROUP BY Store_ID

Upvotes: 2

Related Questions