Click here to Skip to main content
15,868,141 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hello everyone,

I have this query which I use for vb.net forms,
SQL
SELECT   SUM(Price)                    AS 'Total'
         ,CONVERT(char(7), date, 120)  AS 'year'
FROM     [dbo].[Expenses]
WHERE    Department = 'Grocery'
GROUP BY CONVERT(char(7), date, 120)  
HAVING   CONVERT(char(7), date, 120) >= '2017-01'  
AND      CONVERT(char(7), date, 120) <= '2020-01' 


And I am trying to get the Grand Total into a new column so I can use it in my label.text in vb.net forms but I can't seem to do it.
How can I achieve that?

Thankfully

What I have tried:

This is the one I tried:

SQL
SELECT   SUM(Price)                    AS 'Total'
         ,CONVERT(char(7), date, 120)  AS 'year'
FROM     [dbo].[Expenses]
WHERE    Department = 'Grocery'
GROUP BY CONVERT(char(7), date, 120)  
HAVING   CONVERT(char(7), date, 120) >= '2017-01'  
AND      CONVERT(char(7), date, 120) <= '2020-01' 
Posted
Updated 16-Jul-20 0:36am
v4

0) You're using the CONVERT function incorrectly, *and* you're using it where it's not needed.

1) You're using SQL key words for column names. DON'T DO THAT! If you insist on doing it, at least put square brackets around the names.

2) Try it this way.

SQL
SELECT   Sum(Price)     AS Total
         ,YEAR([date])  AS [year]
FROM     [dbo].[Expenses]
WHERE    Department = 'Grocery'
GROUP BY YEAR([date])
WHERE    [date] BETWEEN '2017-01-01' AND '2019-12-31'
 
Share this answer
 
v2
Comments
Member 13410460 16-Jul-20 8:06am    
Hallo realUSOP, Thank you for reply, when i use this code it gives me an error which says 'Incorrrect syntax near the keyword Where ( the second one ) '
#realJSOP 16-Jul-20 8:13am    
well, i can only assume that your date column is a datetime. since we can't devine yuour schema from thin freakin air, you're on the hook to make it actually work.
Member 13410460 16-Jul-20 8:16am    
Yes, it is DateTime, that's why I use CONVERT(char(7), date, 120). Thank you for your reply.
#realJSOP 16-Jul-20 9:55am    
That's invalid. the first argument has to be an expression - either a column name or a variable.
Member 13410460 16-Jul-20 10:02am    
Sure, but in this case, I am talking about the code that you wrote, and it doesn't work.
If you want to get a grand total as a separate row, use GROUP BY ROLLUP:
SQL
SELECT
    Sum(Price) As Total,
    Year([date]) As [year]
FROM
    dbo.Expenses
WHERE
    [date] Between '20170101' And '20191231'
And
    Department = 'Grocery'
GROUP BY ROLLUP
    (Year([date]))
;
GROUP BY (Transact-SQL) - SQL Server | Microsoft Docs[^]

Example output:
Total | Year
------------
   10 | 2017
   20 | 2018
   30 | 2019
   60 | Null
The row with Null in the Year is the grand total.

Edit: To put the grand total in a separate column against each row, use:
SQL
WITH cte As
(
   SELECT
        Sum(Price) As Total,
        Year([date]) As [year]
    FROM
        dbo.Expenses
    WHERE
        [date] Between '20170101' And '20191231'
    And
        Department = 'Grocery'
    GROUP BY
        Year([date])
)
SELECT
    Total,
    [year],
    SUM(Total) OVER () As GrandTotal
FROM
    cte
;
 
Share this answer
 
v3
Comments
Member 13410460 16-Jul-20 8:08am    
Hi Richard Deeming, Thank you for your reply and help, when I use this code it gives me an error which says: ' incorrect syntax near 'year' ' ( the last year)
Richard Deeming 16-Jul-20 8:45am    
Which version of SQL Server are you using?
Member 13410460 16-Jul-20 8:47am    
I am using SQL Server 2012
Richard Deeming 16-Jul-20 8:52am    
You might need extra brackets around the grouping items for a rollup:
GROUP BY ROLLUP (Year([date]))
Member 13410460 16-Jul-20 9:07am    
It works now, it gives me the Total, I tried a lit bit of change and it won't give me all the total between months:
SELECT
Sum(Price) As Total,
CONVERT(char(7), date, 120) As [Year]
FROM
dbo.Expenses
WHERE
CONVERT(char(7), date, 120) Between '2017-01' And '2019-01'
And
Department = 'Grocery'
GROUP BY ROLLUP
( CONVERT(char(7), date, 120))

and it gives me only 2 months of 2018 " cleary there is more then 2 months in 2018"


This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900