January 20, 2012 at 1:06 pm
will you please be explicit? The subquery alone doesn't work:
select fname
,lname
,ssn
,COUNT(*) over (
partition by ssn
) RowCount
from employee
--gives error
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'RowCount'.
-
January 20, 2012 at 1:08 pm
aitchkcandoo (1/20/2012)
will you please be explicit? The subquery alone doesn't work:select fname
,lname
,ssn
,COUNT(*) over (
partition by ssn
) RowCount
from employee
--gives error
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'RowCount'.
ROWCOUNT is a reserved key word. Replace "ROWCOUNT" with rc as already suggested.
That's what I get for posting without testing.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 20, 2012 at 1:11 pm
oh, I see, Rowcount is reserved word for use as a function.
-
January 20, 2012 at 1:15 pm
Good results:
RobSinclaire212671777
LabanMeese212671777
LarryBechold213456123
BarryYoung213456123
-
January 20, 2012 at 6:40 pm
Drew, I read what you said about starting a new thread. With deference to your seniority on this forum, may I disagree? I think it is good to keep the relevant problems together as they have many facets. If a bunch of people gang up on me and say I am utterly wrong and going against the culture and rules here, then I stand corrected.
I am having another 'run in'with the group by clause. I am selecting for two columns, aggregating one and trying to group by the second but still getting group by error.
---this query
USE AdventureWorks2008R2;
GO
SELECT avg(salesytd) OVER(partition BY postalcode)as avgSalesYTD
,a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Person p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.Address a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0
group by a.PostalCode;
GO
---this error
Msg 8120, Level 16, State 1, Line 1
Column 'Sales.SalesPerson.SalesYTD' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
---if I comment out th group by clause I get these results
2720907.883998027
2720907.883998027
2720907.883998027
2720907.883998027
2720907.883998027
2720907.883998027
2337502.081698055
2337502.081698055
2337502.081698055
2337502.081698055
2337502.081698055
2337502.081698055
--- but I wat just one avg per PostalCode.
2720907.883998027
2337502.081698055
How to tweak the query?
-
January 20, 2012 at 9:08 pm
I think you are making things unnecessarily complex, on both levels. The problems you present indeed does not have anything common with the problem stated in your first post of the thread.
Anyway, here is the (simple) code that does what you say you need:
USE AdventureWorks2008R2;
GO
SELECT AVG(s.SalesYTD) AS avgSalesYTD
,a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Person p
ON s.BusinessEntityID = p.BusinessEntityID
INNER JOIN Person.[Address] a
ON a.AddressID = p.BusinessEntityID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0
GROUP BY a.PostalCode
;
GO
January 21, 2012 at 1:48 am
Try this:
use AdventureWorks2008R2 ;
GO
select avg(salesytd) avgSalesYTD,
a.PostalCode
from Sales.SalesPerson s
join Person.Person p on s.BusinessEntityID = p.BusinessEntityID
join Person.Address a on a.AddressID = p.BusinessEntityID
where TerritoryID is not null
and SalesYTD <> 0
group by a.PostalCode
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
January 21, 2012 at 4:31 pm
If you'd like me to repost I will. For me this is all a variation on group by, until I see how you've answered the question. Sure, I'll start a new thread for next question.
The sweet thing was, that you guys still answered my question. I appreciate it, as my quest for SQL query truth is genuine.
-
January 23, 2012 at 7:57 am
aitchkcandoo (1/20/2012)
Drew, I read what you said about starting a new thread. With deference to your seniority on this forum, may I disagree? I think it is good to keep the relevant problems together as they have many facets.
You can certainly disagree, but I believe that the reasons to start a new thread far outweigh the reasons to keep them together.
1) It's clear where the new question begins.
With a new post, the new question is always the first question in the thread. With an old post, the question may be buried several pages deep in the thread.
2) With a new thread, people won't try to answer the wrong question.
When you start a new thread, there is only one question in the thread, so there is only one question to answer. With an old thread, there are multiple questions in a thread and people might answer the original question, especially if the new question is buried several pages deep in the thread (see item 1).
3) With a new thread, it's clear who to ask clarification from.
If there is only one question, there is only one person who can clarify. If there are multiple questions and someone tries to answer the wrong question (see item 2), the may try to solicit information from a poster who is no longer tracking the thread.
4) A new thread can be linked to an old thread.
A new thread can contain a link to an old thread, so that people can review the old thread. While it's true that the old thread doesn't have a link back to the new thread, the other items more than make up for this lack.
5) People with questions are bad at determining how closely related to questions are.
If you have a question on a subject, then you necessarily are lacking a complete understanding of that subject. Without that understanding, it's difficult to make proper determinations.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply