May 26, 2016 at 2:27 pm
I created a query and want to do if it is possible a subquery with summary, below is my code. Is it possible. I have never done a subquery before.
SELECT [GL Description], SUM(Amount) AS [Prior YTD Amount], Year, Month
FROM dbo.vlv_PriorYrDetailExpenses
GROUP BY [GL Description], Year, Month
HAVING (Year = @Year) AND (Month <= @Month)
I get this result using the above query
GL_Description Amount Year Month
Auto Expense 100.00 2015 1
Auto Expense 100.00 2015 2
Off. Expense 200.00 2015 1
Off.Expense 200.00 2015 2
Contract Labor 1000.00 2015 1
Contract Labor 1000.00 2015 2
Cleaning Labor 2000.00 2015 1
Cleaning Labor 2000.00 2015 2
I would like to get this result instead without the month.
GL_Description Amount Year
Auto Expense 200.00 2015
Off. Expense 400.00 2015
Contract Labor 2000.00 2015
Cleaning Labor 4000.00 2015
Any help will be appreciated. Thank you in advance.
May 26, 2016 at 2:32 pm
alex_martinez (5/26/2016)
I created a query and want to do if it is possible a subquery with summary, below is my code. Is it possible. I have never done a subquery before.SELECT [GL Description], SUM(Amount) AS [Prior YTD Amount], Year, Month
FROM dbo.vlv_PriorYrDetailExpenses
GROUP BY [GL Description], Year, Month
HAVING (Year = @Year) AND (Month <= @Month)
I get this result using the above query
GL_Description Amount Year Month
Auto Expense 100.00 2015 1
Auto Expense 100.00 2015 2
Off. Expense 200.00 2015 1
Off.Expense 200.00 2015 2
Contract Labor 1000.00 2015 1
Contract Labor 1000.00 2015 2
Cleaning Labor 2000.00 2015 1
Cleaning Labor 2000.00 2015 2
I would like to get this result instead without the month.
GL_Description Amount Year
Auto Expense 200.00 2015
Off. Expense 400.00 2015
Contract Labor 2000.00 2015
Cleaning Labor 4000.00 2015
Any help will be appreciated. Thank you in advance.
This:
SELECT [GL Description], SUM(Amount) AS [Prior YTD Amount], Year
FROM dbo.vlv_PriorYrDetailExpenses
GROUP BY [GL Description], Year
HAVING (Year = @Year) AND (Month <= @Month)
May 26, 2016 at 3:21 pm
Lynn Pettis (5/26/2016)
This:
SELECT [GL Description], SUM(Amount) AS [Prior YTD Amount], Year
FROM dbo.vlv_PriorYrDetailExpenses
GROUP BY [GL Description], Year
HAVING (Year = @Year) AND (Month <= @Month)
It's going to complain about the Month in that HAVING clause, since you're no longer grouping by Month, although the HAVING criteria should probably be moved to the WHERE clause anyhow.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
May 26, 2016 at 3:38 pm
drew.allen (5/26/2016)
Lynn Pettis (5/26/2016)
This:
SELECT [GL Description], SUM(Amount) AS [Prior YTD Amount], Year
FROM dbo.vlv_PriorYrDetailExpenses
GROUP BY [GL Description], Year
HAVING (Year = @Year) AND (Month <= @Month)
It's going to complain about the Month in that HAVING clause, since you're no longer grouping by Month, although the HAVING criteria should probably be moved to the WHERE clause anyhow.
Drew
Pull the Month criteria out of the HAVING clause. Actually, why is there a HAVING clause instead of a WHERE clause to filter the data?
May 26, 2016 at 3:40 pm
Lynn Pettis (5/26/2016)
drew.allen (5/26/2016)
Lynn Pettis (5/26/2016)
This:
SELECT [GL Description], SUM(Amount) AS [Prior YTD Amount], Year
FROM dbo.vlv_PriorYrDetailExpenses
GROUP BY [GL Description], Year
HAVING (Year = @Year) AND (Month <= @Month)
It's going to complain about the Month in that HAVING clause, since you're no longer grouping by Month, although the HAVING criteria should probably be moved to the WHERE clause anyhow.
Drew
Pull the Month criteria out of the HAVING clause. Actually, why is there a HAVING clause instead of a WHERE clause to filter the data?
SELECT [GL Description], SUM(Amount) AS [Prior YTD Amount], Year
FROM dbo.vlv_PriorYrDetailExpenses
WHERE (Year = @Year) and (Month <= @Month)
GROUP BY [GL Description], Year
May 26, 2016 at 4:18 pm
Thanks so much for help. I really appreciate you helping me out.
May 26, 2016 at 4:20 pm
Thanks for pointing it out to me I really appreciate you helping me out.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply