Click here to Skip to main content
15,063,596 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
Hi Everyone, I have SQL Code which I take the total sum out of it, but the number does not come in full format, what I want from number is to show like this ("1.500,00") and what I get is like this ("1500,00") So for this is my code:


SELECT
        Sum(Price) As Total,
        CONVERT(char(7), date, 120) As [year],
		Department 
    FROM
        dbo.Expenses
    WHERE
       CONVERT(char(7), date, 120) Between '2017-01' And '2019-01'
    And
        Department = 'Grocery' 
    GROUP BY
       CONVERT(char(7), date, 120),
	   Department 

and it shows me the number without a fullstop. The number that i get is for examples 5656,75 and what i would like is to be 5.656,75

What I have tried:

This is the full code that I working on and I don't know where and how to put the format number in the SUM area.

WITH cte As
(
   SELECT
        Sum(Price) As Total,
        CONVERT(char(7), date, 120) As [year],
		Department 
    FROM
        dbo.Expenses
    WHERE
        --CONVERT(char(7), date, 120) Between '2017-01' And '2019-01'
    --And
        Department = 'Grocery' 
    GROUP BY
       CONVERT(char(7), date, 120),
	   Department 
	   )
SELECT
    Total,
    [year],Department as 'Department',
	   Concat(SUM(Total) OVER (),'€') As GrandTotal
FROM
    cte
WHERE
    [year] Is Not Null
;


Thank you for the patience and help,
Gratefully E
Posted
Updated 25-Jul-20 9:15am
v3

Quote:
This is the full code that I working on and I don't know where and how to put the format number in the SUM area.

Short answer: you don't format number inside SQL.
SQL returns the SUM as a number, there is no formatting at this point.
Formatting is done only when convert the number to string or when printing.
   
Comments
Member 13410460 22-Jul-20 13:08pm
   
Thank you for your reply, But how do I change then the format of the number? do I have to work it out on vb.net?
Patrice T 22-Jul-20 13:10pm
   
If VB.net is the language of your app, yes, formatting in the prints in VB app.
Member 13410460 22-Jul-20 13:13pm
   
Thank you sir, I will try that now.
Member 13410460 24-Jul-20 9:54am
   
The Label on my form shows the direct result from SQL Total sum , so I need to format that number. I tried different ways how to do it in my vb.net but I could not find anything useful.
Patrice T 24-Jul-20 10:02am
   
Update your question with exact problem you have and concerned VB code.
Use Improve question to update your question.
Member 13410460 24-Jul-20 10:04am
   
But I dont need to change the question , I still need help regarding how to format the total sum format number in SQL.
Patrice T 24-Jul-20 10:16am
   
Explain exactly what you try to do, with context.
Your solution is not necessary the solution.
Also, tell which GUI component you use.
Member 13410460 24-Jul-20 10:22am
   
So, I use SQL to show informaton into Chart, and what I want now is that when I go to the point showed in vb.net Charts to show me the description of what has been bought that day(something that is not specified in SQL script above). Perhaps a new query that I will write to show me the description and to connect to that query through these lines :

im thisPt As New PointF(CSng(Chartday.Series("Day").Points(Result.PointIndex).XValue),
CSng(Chartday.Series("Day").Points(Result.PointIndex).YValues(0)))

or here as you mention but I dont know how to construct it :

.X = thisPt.X + 1
.Y = thisPt.Y + 1
.Text = thisPt.ToString
I work in VIsual studio 2015.
Thank you for your patience.
Patrice T 24-Jul-20 10:31am
   
Use Improve question to update your question.
So that everyone can pay attention to this information.
Member 13410460 24-Jul-20 10:32am
   
I Did it, Thank you.
You can create your own formatting function for SQL.

Within the function you count the number of chars, in your case, to left of decimal. You can then determine if it's >= 1000 (where you would add begin to need the thousands separator) or just return the value.

If you have to work with it then you need to convert it to a string so you can insert your thousands separator.

Here's the hard part for you: figure out how you will insert multiple separators into your string when every time you add one the length of the string changes.

Now I made one of these myself, for MS SQL:
SET @RVAL = CONVERT(VARCHAR, CAST(@number as Money), 1)

which uses a built-in function to handle this for you. Notice that it's a CAST within a CONVERT and my cast is for USA style: (commas between thousands, period to mark the decimal break).
   
v2

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