T-SQL Number format with no decimals

I have a t-sql I'm using against a table, where I want to count the number of records within a certain category. They are number of accounts, per company. I've been using

SELECT CompanyName, Format(Count(Account), '###,###,###') AS [# Accounts]
FROM tblAccountInformation
GROUP BY CompanyName

My issue is, I copy this out to Excel to use all the time and have to convert it to number once in Excel. The values come out as I want them, using commas to separate to the left of the decimal, but never show the actual decimal values, as it's only ever working with integer values. Is there any way I can have the t-sql format the number the same way, but have it stored as a numeric value, and not as a text value?
8056646Asked:
Who is Participating?
 
8056646Connect With a Mentor Author Commented:
"An SQL result, no matter if MSSQL, Access or any other databse and sql dialect, the columns have a concrete specific type. And to include thousands separators the type can't be integer or numeric, decimal or float or double float. Thousands separators are a formatting character you can only embed into a string type column (char/varchar and more)."

That was all you guys had to say. "You're making a fuss about nothing" I wasn't making a fuss, I have something I'm trying to do, could not find any way of doing it in looking at Microsoft's pages on CAST(), CONVERT(), or FORMAT() so I went to a forum to see if it's able to be done. I never said "oh, this is the worst thing I've ever experienced!" I just said 'this is the annoying thing I'm trying to find a permanent fix for, instead of continually needing to reformat in Excel'. I'm not sure why you have to be so abrasive and make new users not as familiar with the product(s) as you feel so horrible for asking a simple question. But hey thanks - glad to know I'll never come back to this page again.
0
 
NorieVBA ExpertCommented:
Brian

What sort values are you actually dealing with?
0
 
Dustin SaundersDirector of OperationsCommented:
In SQL the number is always stored without the formatting (unless you put it in a varchar column)-- its always done after getting the data.

You can do something like:
SELECT CompanyName,CONVERT(varchar, CAST(Count(Account) AS money), 1) AS [# Accounts]
FROM tblAccountInformation
GROUP BY CompanyName

Open in new window


And get (using 123456 as an example):
123,456.00

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Olaf DoschkeSoftware DeveloperCommented:
Well, Format is creating a string/varchar. If you don't want a string, don't use Format, just Count(Account) as [#Accounts]

Bye, Olaf.
1
 
Mark WillsTopic AdvisorCommented:
An integer still wont show with decimals...
 
Can add that into your FORMAT() try
 select format(12345,'###,###,###.00'), format(12345, 'N', 'en-us')

Open in new window


But Olaf is correct - if you are doing arithmetic in excel, then leave as a number / Integer, and if needing decimals, then CAST rather than FORMAT
select cast(12345 as decimal(9,2))

Open in new window

0
 
8056646Author Commented:
Dustin - Thanks, but CONVERT(varchar, CAST(Count(Account) AS money), 1) AS [# Accounts] does not work for what I need. Using 123456 as example, I want the result to be 123,456 not 123,456.00
No decimals should be visible in the end result.

Olaf - Format() stores it as text, so I don't believe Format() can be the outer function, as I want to copy out (many many results) into Excel and not have to reformat those cells into a number. Plain Count(Account) will return 123456 as the result. I need the result to be 123,456 but copy out to Excel as a number already.

Mark - neither of those ways returned a value stored as a number.

I am looking for a way to get a result in number format, without visible decimals, that uses commas to separate.
0
 
Dustin SaundersDirector of OperationsCommented:
I misread your question, and thought you wanted to show decimal values.

But the answer is the same regarding storing it with commas as I, Olaf, and Mark mentioned.  Numerical values are not stored with any formatting (including commas).  You have to apply the formatting after you get the number value.

You will probably be best served just setting your formatting on columns in excel to the number format you want (or use a macro to set the formatting), and just select COUNT(whatever) as Olaf suggests.
0
 
Olaf DoschkeSoftware DeveloperCommented:
Btian, if your data has a number 123456 and you set an excel cell to it, it's the matter of ecel cellformatting to get 123,456 displayes. If you do the the formatting in SQL you get a tet and excel won't turn text to a number formatted with 1000 separator.

So once and for all, return Count(Account) as [#Accounts] and solve your display problem where it needs to be solved: In Excel, not in SQL. Format the Excel Column as Number instead of "Standard" fomatting and you get your number formatting with 1000 separator AND Excel treats the value as number and you can calculate.

Preformatting means you make the number a string and you don't get Excel to convert this back into a number AND display it with 1000 separator commas, you can convert it, but then the comma will vanish and it will only get back to display with comma 1000s separators, if you use the Excel cell formatting.

Choose your processing steps:

1.
a) format() the number to text in SQL with Format()
b) turn that text back to numeric in Excel
c) turn cell formatting to number in Excel

2.
a) don't format() the number to text in SQL, just return a number
b) turn cell formatting to number in Excel

So is it clear now, what you need?
You need a number if you want a number, and you handle the display formatting in the client, here in Excel, not in SQL.

Bye, Olaf.
0
 
Mark WillsTopic AdvisorCommented:
Ummmm... select cast(12345 as decimal(9,2)) certainly returns a number. But like Dustin, I also misread....

But now with the clarifications above, the options are almost non-existent. how or what do you do to "... copy out to Excel ..."
0
 
Olaf DoschkeSoftware DeveloperCommented:
Sure, Mark, but as he doesn't want decimals that'd just add .00 and excel might even not display that. But count() creates an integer type and that's normally sufficient.Some here have misunderstood the question, yes, that's already cleared up, isn't it?

Brian does copy out this query result to Excel, so he knows how to do that. And Brian, if you wouldn't know, there are tons of ways, either using Excel VBA to make this query and display its result or use any programming language of choice able to make use of MSSQL and automate Excel.

But it's not the concern of SQL to format things for you. The Format() function was introduced in SQL Server 2012. Why so late? Because actually, it's something you don't need on the SQL side of things. Data display is the concern of frontend programming or usage of frontend tools like Excel.

Bye, Olaf.
0
 
Dustin SaundersDirector of OperationsCommented:
@Mark
how or what do you do to "... copy out to Excel ..."

You can work with SQL data directly from Excel without needing to copy anything over.  In your workbook, you can format the table column where the data is shown.

Refer to this guide for more info on how to do that:
https://support.office.com/en-us/article/use-microsoft-query-to-retrieve-external-data-42a2ea18-44d9-40b3-9c38-4c62f252da2e
1
 
Olaf DoschkeSoftware DeveloperCommented:
Nice, Dustin.

In your workbook, you can format the table column where the data is shown
This is shown in the section "Work with the data in Excel", but let me add a more general advice about how to do things in VBA you can do manually in the Excel Sheet: There is a feature called macro recording in Excel, you start recording, manually format a cell to numeric format (with 1000s separator and without decimals) and then you get the VBA code to do the same thing in code, by the way, formatting Column B will result in recorded VBA Code like this:

Columns("B:B").Select
Selection.NumberFormat = "#,##0"

Open in new window


Seeing that you might fire up the Excel help with F1 and search for NumberFormat to get more info on possible formatting options.

Bye, Olaf.
1
 
Mark WillsTopic AdvisorCommented:
@Dustin, @Olaf,

I havent seen anything from Brian that says he has linked to SQL Server, all I know is "I have a t-sql I'm using against a table" and "I copy this out to Excel to use "

Yes, I understand the no decimals, and said so above with "like Dustin, I also misread...."

There isnt anything in T-SQL that I am aware of, so will leave it to you guys to thrash it out....

But still, I am interested in how or what "copy this out to Excel" actually means (because we might be able to do something then)
0
 
8056646Author Commented:
I'm more than aware that I can format in Excel, that's the annoying part of it. The query I use is as a clean-up effort and I'm using it heavily for the next two to three weeks, then never again. I use Excel as a scratchbook to know what I've worked through (the number of accounts, is what we're trying to reduce) writing something to reformat as number for filtering and sorting is annoying, I was wondering if there was any way to format that it would copy out as number as we frequently do use similar queries and it would be of use. Sounds like the consensus here is that no, t-SQL cannot do the formatting on the number I need. Thanks for the help.

I am using it against a SQL server to obtain the data, but for example purposes just gave the basic basics of the query (broken down to only the parts I cared about). "Copy this out to Excel" means I open the pass-through query in Access, select all, open a blank MS Excel and click paste, then work through the issues and delete rows, change sorts and filters based on the number. The data in Excel is never saved, and I can need to obtain the original data from the SQL server multiple times a day, that I was wondering if there was a way of directly formatting the number at the source instead of in Excel. Oh well, I'll just have to deal with the annoyance, or build a Macro to format, only annoying thing is when I now want to look at a different value and add a column to my query, I then need to edit the Macro for that column as well.
0
 
Olaf DoschkeSoftware DeveloperCommented:
> t-SQL cannot do the formatting on the number I need.
Format() does format a number to a string, doesn't it?

I don't see what your core problem is. Execute a query in SQL Server management studio using format() Does that work? Then the problem is somewhere on the way from Access to Excel, perhaps.

Even if the number is interpreted as string (as it is a string) it can be sorted, as alphabetical sorting of a number is the same as numerical sorting, given the thousands separators are at the same places.

You're making a fuss about nothing. What's true is, that Excel can display a number with that formatting and still calculate with that number. That's because excel cells have a value and display value.

An SQL result, no matter if MSSQL, Access or any other databse and sql dialect, the columns have a concrete specific type. And to include thousands separators the type can't be integer or numeric, decimal or float or double float. Thousands separators are a formatting character you can only embed into a string type column (char/varchar and more).

When you work with Excel you have the solution to query Count(Account), get an integer, push that to Excel and format it there.  You have all you need. What is still not understood or problematic about that?

The only problem I see is that Excel does not display a number the way you like it by default. Maybe somewhere in the Excel options, you could make it a default.

Bye, Olaf.
0
 
Olaf DoschkeSoftware DeveloperCommented:
I'm sorry, that you interpret this as abrasive. I'm really just interested to know what you don't understand, so it could be explained better. Because what you said isn't true and I don't get what detail problem you couldn't fix with all the advice you got here.

For example: Did Format() not work, as you are not using SQL 2012 or newer?
Did you do that in Access, whereas it is a T-SQL function and not Access SQL?
Did you get any errors?
People understood your demand wrong in giving you decimal places you didn't want, not your fault. That's unfortunate, but your feedback lacks, for example, what didn't work for you in simply using the simple COUNT(Account) without any cast or convert or format.

Finally, it's your choice to quit in anger, but you'll miss out a lot.

Bye, Olaf.
0
 
Dustin SaundersDirector of OperationsCommented:
Hey @Brian,

I agree, occassionally a problem can get frustrating and lead to some friction- but there is a lot of value in a Q&A site like this (and keep in mind, the Experts who are helping you are doing so for free out of their own time.)

If you look further up in the question, that statement is actually the first advice you get from multiple sources (SQL does not store numbers with formatting, it has to be applied after the fact).

But there were several alternative solutions to your problem (setting a SQL data source and applying formatting in your tables or with 1 click macro).  It would be nice if you could select an answer(s) that provided the most useful information so others with the same question can find the answers later.

I would also suggest that you give the site another try-- now that you know SQL can not store that information as a number with formatting, you can open an Excel topic question and other Experts who spend more time with that product can help you find the most convenient way to handle your data.

-Dustin
0
 
Mark WillsTopic AdvisorCommented:
What if you were to export to a CSV file with your chosen filename. Then all you have to do is double click on the CSV file and it will open in Excel. Can do so with recognised headers so they dont confuse the copy and paste in Excel.

Number columns will be recognised as numeric, strings as text and so on....

Might be worthwhile consideration - might be worthwhile asking another question in Excel TA  :)
0
 
Olaf DoschkeSoftware DeveloperCommented:
There were a lot of misunderstandings in this thread, and that's also typically a reason for people becoming less diligent, including yourself.

For example, Brian, you said:

Olaf - Format() stores it as text, so I don't believe Format() can be the outer function

But I didn't promote the use of Format, in a previous post I said
If you don't want a string, don't use Format, just Count(Account) as [#Accounts]

And that wasn't hiding in a lengthy answer.

To me, part of your anger arose from your own misunderstandings. And can you see how that costs my patience and made me use the phrasing "once and for all" later?

Also, looking back at your question I can see myself why others where giving the advice they were giving:
...but never show the actual decimal values, as it's only ever working with integer values
"never show the actual decimal values" indicates you want decimals. And in regard of "only ever working with integer values": The format you used was for formatting integers, right, you also can't expect anything else, as COUNT() always is an integer, counting numbers are not having decimals. That alone indicates lots of confusions and misunderstandings in the first place. You still don't seem to realize those were removed by us.

So, in the end, why not only give Experts-Exchange and us a second try, but also yourself? As abrasive as this may come over to you, the blame has to be split, here.

Edit: Trying the Format within SQL Server Management Studio it should give you, what you want, in the end, but you might not have noticed whether commas or points are used as separators depends on the Language set in SQL Server:

formatting with english language = comma separatorsformatting with german language = point separatorsformatting with franch language = space separators
So the comma in the format mask is just reflecting the idea of a decimals thousands separator. The textual result of the FORMAT depends on the server setting of a language. The same is true for Excel, as it also depends on the windows locale what it uses as decimal point and separators and so interpreting different versions of these text formatted numbers may result in displaying 123456 or 123 only or 123.45 and I can imagine several others.Notice you never explicitly said what you saw vs what you want.

As far as I see you're from the US, so likely everything is in US locale and US-English language and should be fine, but I don't know if you work remotely for a foreign customer.

Now, if you don't see that as endurance and dilligence to still get at the bottom of your problem, I can't help you.
0
 
8056646Author Commented:
Users didn't help any and were honestly so abrasive I'll just cancel my account and not ask any further questions on this site.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.