Simple subquery

  • 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.

  • 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)

  • 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

  • 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?

  • 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

  • Thanks so much for help. I really appreciate you helping me out.

  • 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