How to get Two averages when the Number is defined as nvarchar ?
-
Hi experts,
Sorry for not getting you a table or data at this moment but I will if I need to get help tomorrow
How do I get
- Average of Cost
2. Average cost of Home Dept vs Overall Average Cost (Example: Home Department)
Average Selling Prices of Each dept against over all selling price average.
-
To convert NVARCHAR to decimal, use something like this:
SELECT Cost = CAST(t1.Cost as Decimal(19,6))
Use AVG() to calculate averages.
For more-specific help, please post the sample data in consumable format, along with desired results.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
and kick the designer in a place where it hurts ...
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
- Johan Bijnens wrote:
and kick the designer in a place where it hurts ...
That would NOT be in the area above the shoulders... they're obviously feeling no pain there. 😀 (Although the product ID is correctly an NVARCHAR and I'm hoping the NVARCHAR isn't actually a part of the column name 🙁 )
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - protocoder wrote:
Hi experts,
Sorry for not getting you a table or data at this moment but I will if I need to get help tomorrow
How do I get
- Average of Cost
2. Average cost of Home Dept vs Overall Average Cost (Example: Home Department)
Average Selling Prices of Each dept against over all selling price average.
To add to Phil's comment, you'd need a GROUP BY, as well. If you want coded help, please read and heed the article located at the first link in my signature line. Thanks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
(Although the product ID is correctly an NVARCHAR and I'm hoping the NVARCHAR isn't actually a part of the column name 🙁 )
Hmm, I think it's a waste to use nvarchar for a basic value like Product ID. Twice the bytes for no gain. Why not just varchar?!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- ScottPletcher wrote:Jeff Moden wrote:
(Although the product ID is correctly an NVARCHAR and I'm hoping the NVARCHAR isn't actually a part of the column name 🙁 )
Hmm, I think it's a waste to use nvarchar for a basic value like Product ID. Twice the bytes for no gain. Why not just varchar?!
Totally agreed.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - ScottPletcher wrote:
Hmm, I think it's a waste to use nvarchar for a basic value like Product ID. Twice the bytes for no gain. Why not just varchar?!
Microsoft Excel, that's why. Well, I'm not sure if that's why in this case but given this example, it might be. If you're exporting stuff to Excel with, say, SSIS, then using NVARCHAR avoids a lot of headache.
- dmbaker wrote:ScottPletcher wrote:
Hmm, I think it's a waste to use nvarchar for a basic value like Product ID. Twice the bytes for no gain. Why not just varchar?!
Microsoft Excel, that's why. Well, I'm not sure if that's why in this case but given this example, it might be. If you're exporting stuff to Excel with, say, SSIS, then using NVARCHAR avoids a lot of headache.
When using SSIS to import from Excel, NVARCHAR is the default character datatype, but that is easily fixed with a Data Conversion component. It's a very small 'headache' & totally worthwhile when compared with the negatives of using NVARCHAR for database columns when VARCHAR is all that is required.
I don't remember having to convert varchars to nvarchars when exporting to Excel, but even if it is necessary, it's still relatively trivial.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
I didn't say "import", I said "export". 🙂
I'm glad you haven't had any issues, I wish my experience was equally "trivial", and introducing a conversion widget in your flow isn't always an easy thing, especially when your table structure is changing or you're dealing with someone else's code. In my specific situation, the tables involved were primarily for exporting to Excel, so using NVARCHAR for those tables made sense and simplified the export considerably. I don't trivially use NVARCHAR, and outside of this situation haven't used it a lot.
- dmbaker wrote:ScottPletcher wrote:
Hmm, I think it's a waste to use nvarchar for a basic value like Product ID. Twice the bytes for no gain. Why not just varchar?!
Microsoft Excel, that's why. Well, I'm not sure if that's why in this case but given this example, it might be. If you're exporting stuff to Excel with, say, SSIS, then using NVARCHAR avoids a lot of headache.
How so?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I experienced a lot of problems with SSIS complaining about being unable to convert data. All I wanted to do was export my table to Excel, and the only way I could get it to work was to convert the fields it was complaining about (VARCHAR fields) to NVARCHAR. I wasn't interested in complicating my data flow with a conversion widget, the structure of this particular table was changing a lot at the time, and continually having to futz with updating the data conversion in SSIS was not something I wanted to deal with. I actually ended up creating a view to do the conversions, and then used that to export from SSIS, that worked pretty well.
So in my case, using NVARCHAR just made things easier. YMMV of course, if you've not experienced similar problems then good for you!
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply