April 18, 2011 at 12:04 am
Hi all,
I want to get average from the below records.
project country vendor cost
------- -------- ------- ----
scm India ven1 50
scm India ven2 10
I need a result like
project country vendor cost avg
------- -------- ------- ---- ---
scm India ven1 50 30
scm India ven2 10 30
April 18, 2011 at 12:16 am
Add some more Sample data
April 18, 2011 at 12:44 am
I believe what you are looking for is this:
create table #TestData (
project char(3),
country varchar(64),
vendor char(4),
cost dec(19,4)
);
insert into #TestData(project,country,vendor,cost)
select 'scm','India','ven1',50 union all
select 'scm','India','ven2',10;
select
project,
country,
vendor,
cost,
avg(cost) over (partition by project) as AvgCost
from
#TestData;
drop table #TestData;
If not, then please provide a much more detailed description of what you are looking for and additional sample data. Please look at the code above not only for an answer but for how you should also present information for us to help you (create table, sample data, etc.).
April 18, 2011 at 12:44 am
Hi,
project country vendor cost
------- -------- ------ ----
scm India ven1 50
scm India ven2 10
abc china ven3 20
abc china ven4 10
Resule like
project country vendor cost avg
------- -------- ------ ---- ---
scm India ven1 50 30
scm India ven2 10 30
abc china ven3 20 15
abc china ven4 10 15
April 18, 2011 at 1:27 am
Hi,
your query works perfectly.I need the same what u send.
Thank you very much..
April 19, 2011 at 11:46 am
CELKO (4/19/2011)
... avoid needless dialect and...
What do you mean exactly? SQL dialects? Why not? This is a SQL Server forum, so there's nothing wrong with some little T-SQL here and there...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2011 at 12:06 pm
Koen Verbeeck (4/19/2011)
CELKO (4/19/2011)
... avoid needless dialect and...What do you mean exactly? SQL dialects? Why not? This is a SQL Server forum, so there's nothing wrong with some little T-SQL here and there...
Koen, Unless I'm wrong, the dialect comment is directed toward the one element of text speak in the OP's third comment. "U" instead of "you".
April 19, 2011 at 12:12 pm
Brandie Tarvin (4/19/2011)
Koen Verbeeck (4/19/2011)
CELKO (4/19/2011)
... avoid needless dialect and...What do you mean exactly? SQL dialects? Why not? This is a SQL Server forum, so there's nothing wrong with some little T-SQL here and there...
Koen, Unless I'm wrong, the dialect comment is directed toward the one element of text speak in the OP's third comment. "U" instead of "you".
Ah. OK then. :unsure:
(I've seen worse abuse of the English language on this site)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 19, 2011 at 12:21 pm
Joe, what leads you to assume that Project+Vendor+Country is a valid PK for this table? There's nothing in the data from the original question that supports that. Isn't it a little premature to make that kind of assumption? You also assume that's the best clustered index for the table (since PK defaults to that and you don't override that in your script), and that's definitely premature at this point in the discussion.
While on the subject of standardizing, shouldn't you also include the schema name in your DDL? You can't assume it's a single-schema database, nor that the default schema for the security context of the current connection is the correct schema for the table you need. You leave a lot up to SQL Server's defaults in your script, and that means you're automatically assuming that ANSI violations are okay or are already cared for. For example, the current SQL Server default is to leave ANSI NULLs off, so the connection default is probably set that way, and you don't override that in your DDL script. Why that partial adherence?
Also, why the assumption on the data type for the vendors? You're taking his sample names, and making assumptions about both normalization patterns, and the data used for that. Why char(5) on that? Why not 4, or 10, or GUIDs for that matter? (4 would hold the samples that were given, but you picked 5. That seems odd to me.)
And why is the cost column assumed to be decimal(5,2)? I again don't see anything in the data provided that supports that assumption, nor would it be standard practice in any published standard I'm aware of, to limit financial data in that particular way.
I'm just curious about what your assumption process is for this kind of thing. You're making a lot of assumptions, and most aren't clear.
I guess I just don't like you shouting "hey, do things the standard way", whilst violation generally accepted best practices, and published standards, yourself. It seems more than a little hypocritical. I'm sure it's not intentional, but if you're trying to set an example, please start doing so.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 19, 2011 at 3:28 pm
Edit: in hindsight, I was inappropriate. Comments removed.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 20, 2011 at 6:23 am
WayneS (4/19/2011)
GSquared (4/19/2011)
Joe ... if you're trying to set an example, please start doing so.Oh, he's setting an example all right - just not the kind that is appreciated out here. The best example he could give now would be to just vanish.
I'd say that's overly harsh.
I'm just looking for explanations on Joe's part as to why he makes the assumptions he does. He's got a lot of know-how and experience, and it's possible those assumptions are defensible. If so, I'd like to see that. If not, I'd like him to add language to his posts that says, "I'm making some assumptions that force me to violate the standards I would otherwise expect to follow," or something to that effect.
Otherwise, as I mentioned, it sets a bad example. It looks hypocritical. I'm pretty sure it's not, but that's not clear from his post.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply