alias w/IN operator

running this now, but need the names, & not emp ID's.
select ticketid, acct, date, createuser
from ticket
where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
group by createuser
will the 'as' work in the IN operator?
where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
'k1004' as Elf) ?
broski wrote:
>running this now, but need the names, & not emp ID's.
>select ticketid, acct, date, createuser
>from ticket
>where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
>group by createuser
>will the 'as' work in the IN operator?
>where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
>'k1004' as Elf) ?
sorry...just signed up & realized i posted in connectivity....meant to be
under reporting.
thank you.

alias w/IN operator

running this now, but need the names, & not emp ID's.
select ticketid, acct, date, createuser
from ticket
where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
group by createuser
will the 'as' work in the IN operator?
where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
'k1004' as Elf) ?broski wrote:
>running this now, but need the names, & not emp ID's.
>select ticketid, acct, date, createuser
>from ticket
>where createuser in ('k1001', 'k1002', 'k1003', 'k1004')
>group by createuser
>will the 'as' work in the IN operator?
>where createuser in ('k1001' as Dave, 'k1002' as Sue, 'k1003' as Santa,
>'k1004' as Elf) ?
sorry...just signed up & realized i posted in connectivity....meant to be
under reporting.
thank you.

Alias not recognized

I'm attempting to refer to an alias in my SELECT clause (within a stored
procedure).
Basically I'm building a string which I will eventually execute by calling
the "exec" statement on my string.
Within my string, I have 3 columns for my SELECT clause. Here is an
extremely watered down example of what I'm referring to.
e.g.
SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
alias_one - alias_two
FROM W,X,Y,Z
The issue is that my select clause does NOT recognize "alias_one" and
"alias_two" as aliases when I call exec(myString).
Can I possibly refer to these columns by index within the sql or possibly
declare these aliases at the beginning of the procedure so that they will be
recognized?
Any help is appreciated.
PK9How do you refer the to alias in your stored procedure. If you post the
code, we might be able to help.
-oj
"PK9" <PK9@.discussions.microsoft.com> wrote in message
news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
> I'm attempting to refer to an alias in my SELECT clause (within a stored
> procedure).
> Basically I'm building a string which I will eventually execute by calling
> the "exec" statement on my string.
> Within my string, I have 3 columns for my SELECT clause. Here is an
> extremely watered down example of what I'm referring to.
> e.g.
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> alias_one - alias_two
> FROM W,X,Y,Z
> The issue is that my select clause does NOT recognize "alias_one" and
> "alias_two" as aliases when I call exec(myString).
> Can I possibly refer to these columns by index within the sql or possibly
> declare these aliases at the beginning of the procedure so that they will
> be
> recognized?
> Any help is appreciated.
> --
> PK9|||This is dynamic SQL creation that uses a cross-tab/pivot, so I'm afraid
posting it may just confuse the issue.
What I end up with at the end of the SQL creation is the following:
'FY 1999' | 'FY 1999 CMP' as two separate columns that are given those
alias' in the stored procedure. Now, I want to say as another column' FY
1999 minus FY 1999 CMP' to give me the difference between the two columns.
Does that help?
"oj" wrote:

> How do you refer the to alias in your stored procedure. If you post the
> code, we might be able to help.
>
> --
> -oj
>
> "PK9" <PK9@.discussions.microsoft.com> wrote in message
> news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
>
>|||Can Anyone help me with this?
I'm really stuck right now.
"oj" wrote:

> How do you refer the to alias in your stored procedure. If you post the
> code, we might be able to help.
>
> --
> -oj
>
> "PK9" <PK9@.discussions.microsoft.com> wrote in message
> news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
>
>|||You are trying to do something like this:
SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
alias_one - alias_two
FROM W,X,Y,Z
This isn't possible in the SQL language. The whole select list happens at th
e same time, logically.
Here's one way with which you don't have to repeat the expressions:
SELECT alias_one, alias_two, alias_one - alias_two
FROM
(
SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
FROM W,X,Y,Z
) AS d
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"PK9" <PK9@.discussions.microsoft.com> wrote in message
news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
> I'm attempting to refer to an alias in my SELECT clause (within a stored
> procedure).
> Basically I'm building a string which I will eventually execute by calling
> the "exec" statement on my string.
> Within my string, I have 3 columns for my SELECT clause. Here is an
> extremely watered down example of what I'm referring to.
> e.g.
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> alias_one - alias_two
> FROM W,X,Y,Z
> The issue is that my select clause does NOT recognize "alias_one" and
> "alias_two" as aliases when I call exec(myString).
> Can I possibly refer to these columns by index within the sql or possibly
> declare these aliases at the beginning of the procedure so that they will
be
> recognized?
> Any help is appreciated.
> --
> PK9|||dynamic sql for xtab. hmmm...that sounds quite familiar. wait, we have such
a commercial solution (http://rac4sql.net) :-)
anyway, you cannot just add the two aliases because the aliases are
calculated at runtime. what you can do is to derive the first query then use
the aliases. take a look at tibor's comment.
-oj
"PK9" <PK9@.discussions.microsoft.com> wrote in message
news:FD1A0770-2736-49B8-B684-16AE1927EF92@.microsoft.com...
> This is dynamic SQL creation that uses a cross-tab/pivot, so I'm afraid
> posting it may just confuse the issue.
> What I end up with at the end of the SQL creation is the following:
> 'FY 1999' | 'FY 1999 CMP' as two separate columns that are given those
> alias' in the stored procedure. Now, I want to say as another column' FY
> 1999 minus FY 1999 CMP' to give me the difference between the two columns.
> Does that help?
> "oj" wrote:
>|||Yoiu missed some basic ideas in SQL. Here is how a SELECT works in SQL
... at least in theory. Real products will optimize things, but the
code has to produce the same results.
a) Start in the FROM clause and build a working table from all of the
joins, unions, intersections, and whatever other table constructors are
there. The table expression> AS <correlation name> option allows you
give a name to this working table which you then have to use for the
rest of the containing query.
b) Go to the WHERE clause and remove rows that do not pass criteria;
that is, that do not test to TRUE (i.e. reject UNKNOWN and FALSE). The
WHERE clause is applied to the working set in the FROM clause.
c) Go to the optional GROUP BY clause, make groups and reduce each
group to a single row, replacing the original working table with the
new grouped table. The rows of a grouped table must be group
characteristics: (1) a grouping column (2) a statistic about the group
(i.e. aggregate functions) (3) a function or (4) an expression made up
those three items.
d) Go to the optional HAVING clause and apply it against the grouped
working table; if there was no GROUP BY clause, treat the entire table
as one group.
e) Go to the SELECT clause and construct the expressions in the list.
This means that the scalar subqueries, function calls and expressions
in the SELECT are done after all the other clauses are done. The
"AS" operator can also give names to expressions in the SELECT
list. These new names come into existence all at once, but after the
WHERE clause, GROUP BY clause and HAVING clause has been executed; you
cannot use them in the SELECT list or the WHERE clause for that reason.
If there is a SELECT DISTINCT, then redundant duplicate rows are
removed. For purposes of defining a duplicate row, NULLs are treated
as matching (just like in the GROUP BY).
f) Nested query expressions follow the usual scoping rules you would
expect from a block structured language like C, Pascal, Algol, etc.
Namely, the innermost queries can reference columns and tables in the
queries in which they are contained.
g) The ORDER BY clause is part of a cursor, not a query. The result
set is passed to the cursor, which can only see the names in the SELECT
clause list, and the sorting is done there. The ORDER BY clause cannot
have expression in it, or references to other columns because the
result set has been converted into a sequential file structure and that
is what is being sorted.
As you can see, things happen "all at once" in SQL, not "from left to
right" as they would in a sequential file/procedural language model. In
those languages, these two statements produce different results:
READ (a, b, c) FROM File_X;
READ (c, a, b) FROM File_X;
while these two statements return the same data:
SELECT a, b, c FROM Table_X;
SELECT c, a, b FROM Table_X;
Think about what a mess this statement is in the SQL model.
SELECT f(c2) AS c1, f(c1) AS c2 FROM Foobar;
That is why such nonsense is illegal syntax.
And dynamic SQL is considered bad programming, not quite as bad as
cursors, but still not the way to do it.|||Thanks, that's exactly what I was looking for!
"Tibor Karaszi" wrote:

> You are trying to do something like this:
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> alias_one - alias_two
> FROM W,X,Y,Z
>
> This isn't possible in the SQL language. The whole select list happens at
the same time, logically.
> Here's one way with which you don't have to repeat the expressions:
> SELECT alias_one, alias_two, alias_one - alias_two
> FROM
> (
> SELECT (X.price * Y.units) AS alias_one, (W.price * Z.units) AS alias_two,
> FROM W,X,Y,Z
> ) AS d
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "PK9" <PK9@.discussions.microsoft.com> wrote in message
> news:98AB1E92-EB82-4155-9DE5-51DEA95CAEEC@.microsoft.com...
>
>sql

Alias in Query

For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as CurrentSchoo
lName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as IsSameScho
ol
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias n
ame used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias n
ame used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony RobinsonHi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as CurrentSchool
Name
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as IsSameScho
ol
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes matter
s worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:u%23cQP0
f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||You reference Users table trice in the joins
FROM zProfile
INNER JOIN USERS as U ON zProfile.UserId = U.UserID
INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
And what is this supposed to evaluate against as it does not do a compare
against anything?
AND WHERE U.UserID = CONVERT(varchar(15), 1)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:It3gf.2741$js5.646@.tornado.rdc-kc.rr.com...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes
matters worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u%23cQP0f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId =
ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinson|||...it's comparing it against a USERID with a value of 1:
AND WHERE U.UserID = CONVERT(varchar(15), 1)
think of it as
AND WHERE U.UserID = CONVERT(varchar(15), @.USERID)
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message news:eEBDbNg7
FHA.3804@.TK2MSFTNGP14.phx.gbl...
You reference Users table trice in the joins
FROM zProfile
INNER JOIN USERS as U ON zProfile.UserId = U.UserID
INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
And what is this supposed to evaluate against as it does not do a compare
against anything?
AND WHERE U.UserID = CONVERT(varchar(15), 1)
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:It3gf.2741$js5.646@.tornado.rdc-kc.rr.com...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender,
u.LastName,
u.FirstName,
dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId = ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Now get this:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias name
used in the query.
It's complaining about the last two lines:
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
I hate aliases...tried every combo. I've aliased it, which only makes
matters worse.
I don't get it...
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:u%23cQP0f7FHA.3984@.TK2MSFTNGP11.phx.gbl...
Hi
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
should be
,PT.ProfileTypeText AS ProfileTypeText
,PT.ProfileDesc AS ProfileDesc
as you aliased zProfileType to PT
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Anthony Robinson" <aconsulting1@.nospam.com> wrote in message
news:6R2gf.2739$js5.459@.tornado.rdc-kc.rr.com...
For the life of me I can't seem to find what's wrong with this query...
SELECT DISTINCT u.userId,
dbo.fn_zGetDefaultUserPhotoID(u.userid) as UserPhotoId,
u.Gender
,u.LastName
,u.FirstName
,dbo.fn_zGetSchoolName(dbo.fn_zGetCurrentSchoolID(u.UserID)) as
CurrentSchoolName
,ud.CurrentYear as CurrentYear
,ud.LastUpdatedDate as ProfileLastUpdated
,dbo.fn_zUserOnlineNow (u.UserId) as OnlineNow
,dbo.fn_zGetMobileAuth(CONVERT(varchar(15), 1)) as TextAuthCurrentUser
,dbo.fn_zGetMobileAuth(u.UserId) as TextAuthResultUser
,dbo.fn_zGetIsFriend( CONVERT(varchar(15), 1), u.UserID) as IsFriend
,dbo.fn_zGetIsSameSchool( CONVERT(varchar(15), 1) ,u.UserID) as
IsSameSchool
,dbo.fn_zGetIsFriend(CONVERT(varchar(15), 1) ,u.UserID) as OnlineFriend
,ud.MemberSince as MemberSince
,dbo.fn_zGetFriendDate( CONVERT(varchar(15), 1) ,u.UserID) as
FriendSince
,zProfile.zProfileId AS ProfileID
,zProfile.CreatedDate AS CreatedDate
,zProfile.Hidden AS Hidden
,zProfileType.ProfileTypeText AS ProfileTypeText
,zProfileType.ProfileDesc AS ProfileDesc
,zProfile.ProfileData AS ProfileData
FROM zProfile, Users u INNER JOIN zUserData ud ON u.UserId =
ud.UserId
INNER JOIN zProfileType PT ON zProfile.zProfileTypeID = PT.zProfileTypeId
INNER JOIN USERS ON zProfile.UserId = Users.UserID
WHERE U.UserID = CONVERT(varchar(15), 1)
Here's the error:
Server: Msg 107, Level 16, State 3, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfileType' does not match with a table name or alias
name used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Server: Msg 107, Level 16, State 1, Line 2
The column prefix 'zProfile' does not match with a table name or alias
name
used in the query.
Any insight would be greatly appreciated!!
Thanks!
--
Anthony Robinsonsql

Alias for expression results within SELECT statements

What are the ways I could make something like this work?

Select Expression1 as 'Column1', Expression2 as 'Column2', Column1 + Column2 as 'Column3'
From table1
Inner join Table2
Group By Criteria1, Criteria2

Where Expression1 and Expression2 are complex expressions.

Exactly the way you have it. (And another variation I'll demonstrate.)

Code Snippet

Select
Expression1 as 'Column1',
Expression2 as 'Column2',
Expression1 + Expression2 as 'Column3'
From table1 t1
Inner join Table2 t2
ON t1.PrimaryKey = t2.ForeignKey
Group By
Criteria1,
Criteria2

Expression 'should' be in parentheses -but not totally necessary.

My preference is this:

Code Snippet

Select
Column1 = ( Expression1 ),
Column2 = ( Expression2 ),
Column3 = ( Expression1 + Expression2 )
From table1 t1
Inner join Table2 t2
ON t1.PrimaryKey = t2.ForeignKey
Group By
Criteria1,
Criteria2

The expression values must be constants, functions(), static values, and data fields from the tables in the query. You cannot use the aliased column names in the expressions since they will not have been materialized yet when called.

|||

You can use a derived table, a view, or a CTE if you are using 2005.

select Column1, Column2, Column1 + Column2 as Columns3

from (

-- here put the initial query

...

) as t

AMB

|||

Code Snippet

Select
Expression1 as 'Column1',
Expression2 as 'Column2',
Expression1 + Expression2 as 'Column3'
From table1 t1
Inner join Table2 t2
ON t1.PrimaryKey = t2.ForeignKey
Group By
Criteria1,
Criteria2

Expression 'should' be in parentheses -but not totally necessary.

My preference is this:

Code Snippet

Select
Column1 = ( Expression1 ),
Column2 = ( Expression2 ),
Column3 = ( Expression1 + Expression2 )
From table1 t1
Inner join Table2 t2
ON t1.PrimaryKey = t2.ForeignKey
Group By
Criteria1,
Criteria2

Neither of these work for me, I get 'Invalid Column Name xxx' error. My expression is a Sum(Case When Then End). Thoughts on how I could make it work in this case?
|||

Of course it is not going to work as presented above. There is no column named 'Expression1' or 'Expression2' in the table.

You need to plug in your expression, real data, and column names BEFORE anything will work.

If you need more detailed help, then you will have to provide more detailed information for us to work with. We can't give you what we don't have.

Please post the entire query as you 'think' it should be, as well as the table DDL, and some sample data in the form of INSERT statements.

|||

Code Snippet

Select IsNull(DepartmentDetails.DepartmentName, 'Total') as 'Division', #tempResourceAllocation.ProjectCategory,
[RequestsStartOfPeriod] = Sum(Case When Condition1 Then 1 End),

[NewRequests] = Sum(Case When Condition2 Then 1 End),
[Completed] = Sum(Case When Condition3 Then 1 End),
[NotRequired] = Sum(Case When Condition4 Then 1 End),

[Total] = ([RequestsStartOfPeriod] + [NewRequests] - [Completed] - [NotRequired])

From #tempResourceAllocation
Right join
(
Subquery here
) DepartmentDetails
On (DepartmentDetails.ProjectCategory = #tempResourceAllocation.ProjectCategory)
Group By #tempResourceAllocation.ProjectCategory, DepartmentDetails.DepartmentName With Rollup

I think there has been a misunderstanding I didn't just copy your example and attempt running it, I just modified my existing query to reflect the method. That's how I got the error. Here's the structure of my query.
|||

Try using a derived table or just re-use the formulas.

select

[Division],

[ProjectCategory],

[RequestsStartOfPeriod],

[NewRequests],

[Completed],

[NotRequired],

[Total] = ([RequestsStartOfPeriod] + [NewRequests] - [Completed] - [NotRequired])
from

(

Select IsNull(DepartmentDetails.DepartmentName, 'Total') as 'Division', #tempResourceAllocation.ProjectCategory,
[RequestsStartOfPeriod] = Sum(Case When Condition1 Then 1 End),

[NewRequests] = Sum(Case When Condition2 Then 1 End),
[Completed] = Sum(Case When Condition3 Then 1 End),
[NotRequired] = Sum(Case When Condition4 Then 1 End)

From #tempResourceAllocation
Right join
(
Subquery here
) DepartmentDetails
On (DepartmentDetails.ProjectCategory = #tempResourceAllocation.ProjectCategory)
Group By #tempResourceAllocation.ProjectCategory, DepartmentDetails.DepartmentName With Rollup

) as t

go

AMB

|||

I still can't see what the Conditions might be. I hope that they are in the form of

WHEN {value} {operator} {value} THEN {value}

WHEN x = y THEN 1.

I feel like I'm working in the dark here. Trying to diagnose something that i can't see...

Please post the entire query, complete error messages, the table DDL, and some sample data in the form of INSERT statements.

And this statement cannot work -you are referring to the expressions by their alias name but they cannot be accessed by the alias name in the scope of the same query -except in the ORDER BY.

[Total] = ([RequestsStartOfPeriod] + [NewRequests] - [Completed] - [NotRequired])

Perhaps you need to explore one or more derived tables to create the results of your 'complex expressions' so that you don't have to keep re-typing them -which is what you would have to do since you cannot use the alias.

|||My conditions are correct, the problem is with using the alias names for the columns. I knew this wouldn't work which is why I posted the question in the first place, but then I misunderstood your first reply to mean that it was indeed possible to use those aliases. I'm new at this so I second guess myself easily.

I will use a derived table, it's not a big deal.

Thanks for the input.

alias

How to get more columns within same alias?

(
select DateOpen AS Date,TestObjectID from RprRepair where TestObjectID = @.AssetID
union all
select DateSent ,TestObjectID from RprRepair where TestObjectID = @.AssetID
union all
select DateRepairFinished,TestObjectID from RprRepair where TestObjectID = @.AssetID
) AS Der

This works fine alone, but when i put it into union i get an error that no more than one value can be in subqueries.

Could you please explain what you are trying to do? I don't quite understand your question. What do you mena by get more columns with same alias?|||

Then use join rather than correlated subquery.

select

...

, Der.* -- here you can use all the cols from Der

from ...

inner/left/right join (

select DateOpen AS Date,TestObjectID from RprRepair where TestObjectID = @.AssetID

union all

select DateSent ,TestObjectID from RprRepair where TestObjectID = @.AssetID

union all

select DateRepairFinished,TestObjectID from RprRepair where TestObjectID = @.AssetID

) AS Der on <conditions>

Aggregation functon

Hi,
I have query SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
And now I need
SELECT * FROM Table WHERE
row is equal to row which was used to calculate output of result from
SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
It suffices me this
SELECT *, MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
but it is not legal.
Thank for your suggestionsI think the easiest way to do this would be :
SELECT TOP 1 SQRT(X1*X2+Y1*Y2), *
FROM Table
ORDER BY SQRT(X1*X2+Y1*Y2) DESC
This will return you the first line only where SQRT() is the biggest. Mind
that if you want to have ALL lines where SQRT reaches it's maximum, then
you'll want this :
SELECT SQRT(X1*X2+Y1*Y2), *
FROM Table
WHERE SQRT(X1*X2+Y1*Y2) = (SELECT MAX(SQRT(X1*X2+Y1*Y2))
FROM Table)
Good luck
Roby
"B.J." wrote:

> Hi,
> I have query SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> And now I need
> SELECT * FROM Table WHERE
> row is equal to row which was used to calculate output of result from
> SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> It suffices me this
> SELECT *, MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> but it is not legal.
> Thank for your suggestions|||something like this:
select *
from table
where pk = (select top 1 pk from table order by SQRT(X1*X2+Y1*Y2) desc)
where 'pk' is the table's primary key.
dean
"B.J." <BJ@.discussions.microsoft.com> wrote in message
news:67E9D409-A13A-4982-A7EA-74A946A42542@.microsoft.com...
> Hi,
> I have query SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> And now I need
> SELECT * FROM Table WHERE
> row is equal to row which was used to calculate output of result from
> SELECT MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> It suffices me this
> SELECT *, MAX ( SQRT(X1*X2+Y1*Y2) ) FROM Table;
> but it is not legal.
> Thank for your suggestions|||SELECT x1, x2, y1, y2, SQRT(x1*x2 + y1*y2)
FROM YourTable
WHERE x1*x2 + y1*y2 =
(SELECT MAX(x1*x2 + y1*y2)
FROM YourTable)
The SQRT() function is redundant in the subquery so I've left it out
here to save a few cycles.
David Portas
SQL Server MVP
--|||Another way to get all rows with the maximum X1*X2+Y1*Y2 is
SELECT TOP 1 WITH TIES SQRT(X1*X2+Y1*Y2), *
FROM T
ORDER BY SQRT(X1*X2+Y1*Y2) DESC
Steve Kass
Drew University
deroby wrote:
>I think the easiest way to do this would be :
>SELECT TOP 1 SQRT(X1*X2+Y1*Y2), *
> FROM Table
>ORDER BY SQRT(X1*X2+Y1*Y2) DESC
>This will return you the first line only where SQRT() is the biggest. Mind
>that if you want to have ALL lines where SQRT reaches it's maximum, then
>you'll want this :
>SELECT SQRT(X1*X2+Y1*Y2), *
> FROM Table
> WHERE SQRT(X1*X2+Y1*Y2) = (SELECT MAX(SQRT(X1*X2+Y1*Y2))
> FROM Table)
>Good luck
>Roby
>
>"B.J." wrote:
>
>

Aggregates and subqueries

In the pubs database I need to find all the books whose total sales (qty)
exceed the average total sales
I came up with this query...
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is there a better way to write this?
Hi David
Why are you JOINING with the titles table? There is nothing in that table
you are using. Sales has a title_id. You would only have to JOIN to titles
if you wanted the book title.
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"David F" <davef@.nksj.ru> wrote in message
news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
> In the pubs database I need to find all the books whose total sales (qty)
> exceed the average total sales
> I came up with this query...
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> Is there a better way to write this?
>
|||Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>
|||Not quite. Check your GROUP BY:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"David F" <davef@.nksj.ru> wrote in message news:OzHtRagGEHA.3816@.TK2MSFTNGP12.phx.gbl...
Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>

Aggregates and subqueries

In the pubs database I need to find all the books whose total sales (qty)
exceed the average total sales
I came up with this query...
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is there a better way to write this?Hi David
Why are you JOINING with the titles table? There is nothing in that table
you are using. Sales has a title_id. You would only have to JOIN to titles
if you wanted the book title.
SELECT t.title_id, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"David F" <davef@.nksj.ru> wrote in message
news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
> In the pubs database I need to find all the books whose total sales (qty)
> exceed the average total sales
> I came up with this query...
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> Is there a better way to write this?
>|||Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>|||Not quite. Check your GROUP BY:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"David F" <davef@.nksj.ru> wrote in message news:OzHtRagGEHA.3816@.TK2MSFTNGP1
2.phx.gbl...
Whoops
It should have read:
SELECT t.title, sum(s.qty)
FROM titles t JOIN sales s ON s.title_id=t.title_id
GROUP BY t.title_id
HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
Is this correct?
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:ONEIiOfGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi David
> Why are you JOINING with the titles table? There is nothing in that table
> you are using. Sales has a title_id. You would only have to JOIN to titles
> if you wanted the book title.
> SELECT t.title_id, sum(s.qty)
> FROM titles t JOIN sales s ON s.title_id=t.title_id
> GROUP BY t.title_id
> HAVING sum(s.qty) > (SELECT avg(qty) FROM sales)
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "David F" <davef@.nksj.ru> wrote in message
> news:Oe8nbFfGEHA.2472@.TK2MSFTNGP10.phx.gbl...
(qty)
>

Aggregate where claus problem =/

Hi Guys,

Is it possible to have a where clause (or other method) where you only select the max value from this: SUM(ORDER_ITEM.ItemQuantity) and only output that 1 row... or even perhaps a range of rows... in others words... find the ItemID with the greatest combined Quantity

Heres the query so far:

SELECT ORDER_ITEM.ItemID, SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID

Thx for reading :-)

--PhilkillsTry this:
SELECT itemid, max(qsum)
FROM
(
SELECT ORDER_ITEM.ItemID, SUM(ORDER_ITEM.ItemQuantity) AS qsum
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
)
group by itemid|||Shammat, I don't think that will work. The outer query will return the same multi-record dataset as the inner query.

Try this:
SELECT TOP 1
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||Shammat, I don't think that will work. The outer query will return the same multi-record dataset as the inner query.

Try this:
SELECT TOP 1
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

ah thx man perfect ^^

Although, is it not inefficient to have 2 sums for the same thing? or will SQL realise that their the same thing and count them as such..

Also would be it possible to change the query to select all Total's that are greater than say... 10..

like:

SELECT
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
WHERE SUM(ORDER_ITEM.ItemQuantity) > 10
GROUP BY ORDER_ITEM.ItemID
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

although this doesn't work as it doesn't like that in the where clause =/|||Doing multiple aggregate is not expensive. Doing multiple data-scans is what costs query time.

To filter on an aggregate value you must use the HAVING clause:
SELECT
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
HAVING SUM(ORDER_ITEM.ItemQuantity) > 10
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC|||Doing multiple aggregate is not expensive. Doing multiple data-scans is what costs query time.

To filter on an aggregate value you must use the HAVING clause:
SELECT
ORDER_ITEM.ItemID,
SUM(ORDER_ITEM.ItemQuantity) AS 'Max'
FROM ORDER_ITEM
GROUP BY ORDER_ITEM.ItemID
HAVING SUM(ORDER_ITEM.ItemQuantity) > 10
ORDER BY SUM(ORDER_ITEM.ItemQuantity) DESC

thx mate, lol seems kinda pointless "having" an extra keyword just to perform those extra operations ;p (that is instead of where)

Aggregate String Concatenation function?

Hi,

I'm trying to do the following, but am getting errors because (obviously) SUM doesn't work with data types of nvarchar.

SELECT
SUM(CASE WHEN FieldName = 'SPECIFIC' THEN Tolerance ELSE '' END) AS 'Specific Tolerance'
FROM FIELD_TOLERANCE
GROUP BY Area

Tolerance holds values such as '100 +/- 25'. Obviously the first thought would be to seperate the two parts '100' and '25' into seperate fields and then have the program reconstruct it. Unfortunatly sometimes the value is odd, such as '100 +10 -25' (meaning a range of 75 - 110 with a target of 100).

Is there any way to put effectively sum up the Tolerance. Also, I know for a fact the FieldName 'SPECIFIC' will only be in the database once for each area.

Thanks,
RyanI searched Books Online for aggregate functions as well as just functions. I found nothing listed under either which would help.

Should I create a function for this task? How would I create a function to do this?

Thanks,
Ryan|||We no longer need to do what I was asking about. But is there a way? I'm curious.|||If there is only one 'SPECIFIC' value per area, are you really adding anything together? Not being in your field, I am having a problem getting my mind around adding tolerances together. It may be that sum is not the right function for this application. What is the result set you want in the end?|||Originally posted by MCrowley
If there is only one 'SPECIFIC' value per area, are you really adding anything together? Not being in your field, I am having a problem getting my mind around adding tolerances together. It may be that sum is not the right function for this application. What is the result set you want in the end?

well I have a table like so (dashes inserted for web formating purposes):

Area--Field--Tolerance
1----FieldA--100 +/- 12
1----FieldB--100 +/- 13
2----FieldA--97 +3 -7
2----FieldC--95 +/- 5

Area type = int
Field type = varchar
Tolerance type = varchar

I want the results I want are as follows (dashes inserted for web formating purposes):

Area--FieldATolerance--FieldBTolerance--FieldCTolerance
1----100 +/- 12---100 +/-13
2----97 +3 -7----------95 +/- 5

This way I can pass the values for field I want the tolerances for and get them all back in one record. This allows the Tolerance table to hold tolerances for different fields, yet make retrieving the tolerances easy.

-Ryan

ps. Thanks for the reply

Aggregate problem

I am getting the following error in my SQL.
Column 'dbo.ClientWorkerStatus.WorkerTypeID' is invalid in the select list
because it is not contained in either an aggregate function or the GROUP BY
clause.
My code is below. What I want to get is up to 1, 2 or 3 counts for one
ClientID as there are multiples in the ClientWorkerStatus table.
SELECT dbo.ClientWorkerStatus.ClientID,
Preferred = CASE
WHEN dbo.ClientWorkerStatus.WorkerTypeID = 0 THEN 1
ELSE 0
END,
Family = CASE
WHEN dbo.ClientWorkerStatus.WorkerTypeID = 1 THEN 1
ELSE 0
END,
Pool = CASE
WHEN dbo.ClientWorkerStatus.WorkerTypeID = 2 THEN 1
ELSE 0
END
FROM dbo.ClientWorkerStatus INNER JOIN
dbo.ClientWorkerLink ON dbo.ClientWorkerStatus.ClientID =
dbo.ClientWorkerLink.ClientID AND
dbo.ClientWorkerStatus.WorkerID = dbo.ClientWorkerLink.WorkerID
WHERE (dbo.ClientWorkerStatus.StatusEnd IS NULL OR
dbo.ClientWorkerStatus.StatusEnd > GETDATE()) AND
(dbo.ClientWorkerLink.Active = 1)
GROUP BY dbo.ClientWorkerStatus.ClientID
Thanks, DavidWhat aggregate?
Have you tried something like:
SELECT s.ClientID,
Preferred = CASE s.WorkerTypeID WHEN 0 THEN 1 ELSE 0 END,
Family = CASE s.WorkerTypeID WHEN 1 THEN 1 ELSE 0 END,
Pool = CASE s.WorkerTypeID WHEN 2 THEN 1 ELSE 0 END
FROM
dbo.ClientWorkerStatus s
INNER JOIN dbo.ClientWorkerLink l
ON s.ClientID = l.ClientID
AND s.WorkerID = l.WorkerID
WHERE
COALESCE(s.StatusEnd, '20300101') > GETDATE()
AND l.Active = 1
GROUP BY
s.ClientID,
CASE s.WorkerTypeID WHEN 0 THEN 1 ELSE 0 END,
CASE s.WorkerTypeID WHEN 1 THEN 1 ELSE 0 END,
CASE s.WorkerTypeID WHEN 2 THEN 1 ELSE 0 END
Without DDL, sample data and desired results, this is only a guess. See
http://www.aspfaq.com/5006 for info on providing details that will yield a
full solution.|||Hello, David
Is the ClientI=ADD column the primary key (or a unique key) in the
ClientWorkerStatus table ? If yes, you can safely add the
WorkerT=ADypeID column to the GROUP BY list, i.e:
SELECT [...]
GROUP BY dbo.ClientWorkerStatus.ClientI=ADD,
dbo.ClientWorkerStatus.WorkerT=ADypeID=20
Razvan|||Erase your group by at the end of your query.
You don't have any sum, avg, .... in your query. The group by clause is not
required.
Jonathan
"Razvan Socol" wrote:

> Hello, David
> Is the ClientI_D column the primary key (or a unique key) in the
> ClientWorkerStatus table ? If yes, you can safely add the
> WorkerT_ypeID column to the GROUP BY list, i.e:
> SELECT [...]
> GROUP BY dbo.ClientWorkerStatus.ClientI_D,
> dbo.ClientWorkerStatus.WorkerT_ypeID
> Razvan
>|||Yes, Aaron...your's worked. However I was also able to get it to work
by surrounding the CASE statements in the SELECT clause by SUM(...).
For example:
Preferred = SUM(CASE s.WorkerTypeID WHEN 0 THEN 1 ELSE 0 END), ...etc
I do want to group the results by ClientID because any one ClientID can
have more than 1 WorkerTypeID so I need the count of each.
Thanks.
David
*** Sent via Developersdex http://www.examnotes.net ***|||Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
You have absolutely no idea how to design a schema, do you?
You have columns named "-type_id" which make no sense. Look at the
ISO-11179 Standards.
You have table names that end with "-link", as if you were writing a
1970's navigation database.
You have table of status codes when status is a kind of attribute, not
an entity.
Then after all those FUNDAMENTAL errors, you do not have any aggregates
in the useless crap you posted.
dbo.ClientWorkerStatus.StatusE=ADnd > GETDATE()) <<
You might want to get enough basic SQL programming skills to write this
as:
COALESCE (foo_status_end_date, CURRENT_TIMESTAMP) >=3D CURRENT_TIMESTAMP
There is no such thing as a magical, universal "status": -- it is the
status of **something**. That getdate() is a proprietary syntax which
good SQL programmers avoid like lice. Learn the options in the
language so you can write them in SQL and not in some 3GL-style syntax.

Aggregate on an aggregate

I need to get the sum of a field that already has an aggregate function (MAX) performed on it. I am using the following query

Code Snippet

SELECT "tI"."ItemID", MAX("vSS"."ShortDesc") "Short Description",

MAX("tPCT"."FreezeQty") "Freeze Qty", SUM("vSS"."QtyOnHand") "Current Qty",

"tPCT"."BatchKey"

FROM ("vSS" "vSS"

INNER JOIN "tI" "tI"

ON "vSS"."ItemKey"="tI"."ItemKey")

LEFT OUTER JOIN "tPCT" "tPCT"

ON "vSS"."ItemKey"="tPCT"."ItemKey"

WHERE "vSS"."ItemID" = '3002954'

GROUP BY "tI"."ItemID", "tPCT"."BatchKey"

It yields the following results

ItemID Short Description Freeze Qty Current Qty BatchKey 3002954 SET, WRENCH HEX METRIC -33 129 42221 3002954 SET, WRENCH HEX METRIC 51 129 42244 3002954 SET, WRENCH HEX METRIC -31 129 42250

I need to SUM the maximum freeze quantity values per item ID. Therefore for this record, I need the following results:

3002954 SET, WRENCH HEX METRIC -13 129

Can this be done via a subquery? Any assistnance would be greatly appreciated?

Thanks,

DLee

I would think you could create a subquery using the following;

SELECT "ItemID","Short Description","Current Qty", SUM("Freeze Qty")

FROM ("vSS");

|||

Donna, try this query

SELECT ItemID

, MAX(SQ.ShortDesc) as [Short Description]

, SUM(SQ.FreezeQty) as [Freeze Qty]

, MAX(SQ.[Current Qty]) as [Current Qty]

FROM (

SELECT tI.ItemID

, MAX(vSS.ShortDesc)

, MAX(tPCT.FreezeQty)

, SUM(vSS.QtyOnHand)

, tPCT.BatchKey

FROM vSS INNER JOIN tI

ON vSS.ItemKey=tI.ItemKey

LEFT OUTER JOIN tPCT

ON vSS.ItemKey=tPCT.ItemKey

WHERE vSS.ItemID = '3002954'

GROUP BY tI.ItemID, tPCT.BatchKey

) SQ

GROUP BY SQ.ItemID

|||

Hello Donna,

Tweak your query a little and try this...

SELECT "tI"."ItemID", "vSS"."ShortDesc" "Short Description",

SUM("tPCT"."FreezeQty") "Freeze Qty", "vSS"."QtyOnHand" "Current Qty"

FROM ("vSS" "vSS"

INNER JOIN "tI" "tI"

ON "vSS"."ItemKey"="tI"."ItemKey")

LEFT OUTER JOIN "tPCT" "tPCT"

ON "vSS"."ItemKey"="tPCT"."ItemKey"

WHERE "vSS"."ItemID" = '3002954'

GROUP BY "tI"."ItemID", "vSS"."ShortDesc", "vSS"."QtyOnHand"

Hope this helps.

Regards.....

|||

If you do not need BatchKey, you can just remove it from the query and you should get the desired answer.

e.g.

Code Snippet

SELECT "tI"."ItemID", MAX("vSS"."ShortDesc") "Short Description",

MAX("tPCT"."FreezeQty") "Freeze Qty", SUM("vSS"."QtyOnHand") "Current Qty"

FROM ("vSS" "vSS"

INNER JOIN "tI" "tI"

ON "vSS"."ItemKey"="tI"."ItemKey")

LEFT OUTER JOIN "tPCT" "tPCT"

ON "vSS"."ItemKey"="tPCT"."ItemKey"

WHERE "vSS"."ItemID" = '3002954'

GROUP BY "tI"."ItemID"

|||

This did the trick!

Thanks Gopi!

Aggregate on an aggregate

I need to get the sum of a field that already has an aggregate function (MAX) performed on it. I am using the following query

Code Snippet

SELECT "tI"."ItemID", MAX("vSS"."ShortDesc") "Short Description",

MAX("tPCT"."FreezeQty") "Freeze Qty", SUM("vSS"."QtyOnHand") "Current Qty",

"tPCT"."BatchKey"

FROM ("vSS" "vSS"

INNER JOIN "tI" "tI"

ON "vSS"."ItemKey"="tI"."ItemKey")

LEFT OUTER JOIN "tPCT" "tPCT"

ON "vSS"."ItemKey"="tPCT"."ItemKey"

WHERE "vSS"."ItemID" = '3002954'

GROUP BY "tI"."ItemID", "tPCT"."BatchKey"

It yields the following results

ItemID Short Description Freeze Qty Current Qty BatchKey 3002954 SET, WRENCH HEX METRIC -33 129 42221 3002954 SET, WRENCH HEX METRIC 51 129 42244 3002954 SET, WRENCH HEX METRIC -31 129 42250

I need to SUM the maximum freeze quantity values per item ID. Therefore for this record, I need the following results:

3002954 SET, WRENCH HEX METRIC -13 129

Can this be done via a subquery? Any assistnance would be greatly appreciated?

Thanks,

DLee

I would think you could create a subquery using the following;

SELECT "ItemID","Short Description","Current Qty", SUM("Freeze Qty")

FROM ("vSS");

|||

Donna, try this query

SELECT ItemID

, MAX(SQ.ShortDesc) as [Short Description]

, SUM(SQ.FreezeQty) as [Freeze Qty]

, MAX(SQ.[Current Qty]) as [Current Qty]

FROM (

SELECT tI.ItemID

, MAX(vSS.ShortDesc)

, MAX(tPCT.FreezeQty)

, SUM(vSS.QtyOnHand)

, tPCT.BatchKey

FROM vSS INNER JOIN tI

ON vSS.ItemKey=tI.ItemKey

LEFT OUTER JOIN tPCT

ON vSS.ItemKey=tPCT.ItemKey

WHERE vSS.ItemID = '3002954'

GROUP BY tI.ItemID, tPCT.BatchKey

) SQ

GROUP BY SQ.ItemID

|||

Hello Donna,

Tweak your query a little and try this...

SELECT "tI"."ItemID", "vSS"."ShortDesc" "Short Description",

SUM("tPCT"."FreezeQty") "Freeze Qty", "vSS"."QtyOnHand" "Current Qty"

FROM ("vSS" "vSS"

INNER JOIN "tI" "tI"

ON "vSS"."ItemKey"="tI"."ItemKey")

LEFT OUTER JOIN "tPCT" "tPCT"

ON "vSS"."ItemKey"="tPCT"."ItemKey"

WHERE "vSS"."ItemID" = '3002954'

GROUP BY "tI"."ItemID", "vSS"."ShortDesc", "vSS"."QtyOnHand"

Hope this helps.

Regards.....

|||

If you do not need BatchKey, you can just remove it from the query and you should get the desired answer.

e.g.

Code Snippet

SELECT "tI"."ItemID", MAX("vSS"."ShortDesc") "Short Description",

MAX("tPCT"."FreezeQty") "Freeze Qty", SUM("vSS"."QtyOnHand") "Current Qty"

FROM ("vSS" "vSS"

INNER JOIN "tI" "tI"

ON "vSS"."ItemKey"="tI"."ItemKey")

LEFT OUTER JOIN "tPCT" "tPCT"

ON "vSS"."ItemKey"="tPCT"."ItemKey"

WHERE "vSS"."ItemID" = '3002954'

GROUP BY "tI"."ItemID"

|||

This did the trick!

Thanks Gopi!

Aggregate multiple columns with different SELECT criteria

Let me start with saying thanks to all of you who have helped me (I'm a SQL newbee after doing OO for the past 12+ years)

I need to do several aggregates on multiple columns, with each column having different SELECT Criteria.

Sample Data:

Dept Project Cost CostFlag Schedule ScheduleFlag
D1 D1P1 495 1 135 3
D1 D1P2 960 2 70 2
D1 D1P3 1375 3 105 2
D1 D1P4 1050 2 160 3
D1 D1P5 1890 3 40 1

D2 D2P1 650 1 155 3
D2 D2P2 890 2 125 2
D2 D2P3 1235 3 85 1
D2 D2P4 430 1 140 3

D3 D3P1 1960 3 45 1
D3 D3P2 1490 3 85 1
D3 D3P3 1025 2 135 3
D3 D3P4 615 1 100 2
D3 D3P5 270 1 70 1
D3 D3P6 815 2 155 3

I need to calculate MEAN (average), Standard Deviation, Variance, Range, Span & Median for each data column (Cost, Schedule in the test data), where each data column has different selection criteria. I have the calculations working for each column individually (e.g. funcCalcCost, funcCalcSchedule), but I need to return the calculated values as a single data set:

SELECT Dept, Project, AVG(Cost) as Cost_Mean, MAX(Cost) - MIN(Cost) as Cost_Range, .......

WHERE CostFlag = @.InputParameter

GROUP BY Dept, Project

The code above works great - but only for a single column. I need to return a dataset like this:
Dept Project Cost_Mean Cost_Range
D1 D1P1 495 135
D1 D1P2 960 70
D1 D1P3 1375 105

I need to return a dataset like this:

Dept Project Cost_Mean Cost_Range Schedule_Mean Schedule_Range
D1 D1P1 495 135 100 28
D1 D1P2 960 70 42 12
D1 D1P3 1375 105 91 38

I also have working code calculate the MEDIAN (what a pain that was, thank god I found a code example to get me going on the MEDIAN)

Thanks!

Did you try

SELECT Dept, Project, AVG(Cost) as Cost_Mean, MAX(Cost) - MIN(Cost) as Cost_Range,AVG(Schedule) as Schedule_Mean,MAX(Schedule) - MIN(Schedule) as Schedule_Range
WHERE CostFlag = @.InputParameter

GROUP BY Dept, Project

?

|||The query above will not produce correct results.

Each data column being aggregating has its own unique "Flag" column (Cost - CostFlag, Schedule - ScheduleFlag)- the value of the "Flag" column determines if that record should be included in the dataset to be used during the aggregation.

I do have seperate individual queries that produce the correct aggregated values, but only for 1 specific data column.

There is a different SELECT condition for each column of data I am trying to process:

Below are 2 queries and their resulting datasets:

Aggregate Cost:

SELECT Dept, Project, AVG(Cost) as Cost_Mean, MAX(Cost) - MIN(Cost) as Cost_Range, .......

WHERE CostFlag = 2

GROUP BY Dept, Project

Dataset returned:
Dept Project Cost_Mean Cost_Range
D1 D1P1 495 135
D1 D1P2 960 70
D1 D1P3 1375 105

Aggregate Schedule:

SELECT Dept, Project, AVG(Schedule) as Schedule_Mean, MAX(Schedule) - MIN(Schedule) as Schedule_Range, .......

WHERE ScheduleFlag = 3

GROUP BY Dept, Project

Dataset returned:
Dept Project Schedule_Mean Schedule_Range
D1 D1P1 100 28
D1 D1P2 42 12
D1 D1P3 91 38

I need to return a single dataset with all of the calculated columns - like this:

Dept Project Cost_Mean Cost_Range Schedule_Mean Schedule_Range
D1 D1P1 495 135 100 28
D1 D1P2 960 70 42 12
D1 D1P3 1375 105 91 38

Could I somehow use a JOIN to combine the 2 datasets produced by the 2 different queries?

Thanks everyone for your help

|||

Now, if you explained more clearly, the things are very easy to do :

1.build with your last 2 selects 2 views :

viewCost and viewSchedule

2. you can use the following query :

select a.Dept,a.Project,Cost_Mean,Cost_Range,Schedule_Mean, Schedule_Range

from viewCost a innner join viewSchedule b on a.Dept=b.Dept and a.Project=b.Project

|||I'm lost on using 2 views & the query (I am a total T-SQL greanbean, learning as I go on this project).

I have no idea where to code the WHERE conditions to select based upon each of the "Flag" values?

This is how I am thinking about this (and I could be way way way off base here):

I need to have 2 different datasets - one for Cost and one for Schedule. The Where clause in the Query for the 2 datasets is different: using different columns in the condition.

for example:
for the "cost" query : WHERE CostFlag = 2 for the "schedule" query: WHERE ScheduleFlag = 3
|||

You can just derive them.

e.g.

Code Snippet

select *

from(

SELECT Dept, Project, AVG(Cost) as Cost_Mean, MAX(Cost) - MIN(Cost) as Cost_Range, ...

WHERE CostFlag = 2
GROUP BY Dept, Project

) tb1

join (
SELECT Dept, Project, AVG(Schedule) as Schedule_Mean, MAX(Schedule) - MIN(Schedule) as Schedule_Range, .......

WHERE ScheduleFlag = 3
GROUP BY Dept, Project
) tb2

on tb1.Dept=tb2.Dept and tb1.Project=tb2.Project

|||Thanks

Using derived tables did the trick!

Aggregate function for select statement result?

Ok, for a bunch of cleanup that i am doing with one of my Portal Modules, i need to do some pretty wikid conversions from multi-view/stored procedure calls and put them in less spid calls.

currently, we have a web graph that is hitting the sql server some 60+ times with data queries, and lets just say, thats not good. so far i have every bit of data that i need in a pretty complex sql call, now there is only one thing left to do.

Problem:
i need to call an aggregate count on the results of another aggregate function (sum) with a group by.

*ex: select count(select sum(Sales) from ActSales Group by SalesDate) from ActSales

This is seriously hurting me, because from everything i have tried, i keep getting an error at the second select in that statement. is there anotherway without using views or stored procedures to do this? i want to imbed this into my mega sql statement so i am only hitting the server up with one spid.

thanks,
Tom Anderson
Software Engineer
Custom Business SolutionsWell, i may be able to pull this off, and do an interim call, but i wanted to try to keep it as single sql as possible, seems microsoft even documents that you can't contain a select or aggregate from within an aggregate...

wish that would change, anywho, here is my call on a test database.

SELECT SUM(listprice) FROM tblOrderSales where listprice > 0 GROUP BY [Job Name];
select @.@.rowcount, [Job Name] from tblOrderSales;

man that is simple, but still 2 calls, if anyone knows how to change it to one, i would be VERY much appreciative.|||ok, well, that works well if building a function, procedure, or view, but my vb.net code is only returning the first select statement.

any ideas?|||OK, solved...

Funny how that works, wait long enough, and search hard enough, and eventually you figure out your own problems...

Well, decided to post the full thing here.

Old Code: Implements View for subData

viewShowSales:
SELECT [Shop ID] AS ShopID, SUM(ListPrice) AS Sales
FROM dbo.tblOrderSales
WHERE (ListPrice > 0)
GROUP BY [Shop ID]

Old SelectStatement to make it work:
select sum(viewShopSales.Sales) / count(viewShopSales.Sales) as AvgSales from tblOrderSales c, viewShopSales

New Code: No view needed, and can plop in as a value in any subQuery situation (like i need).

select (Sub1.AvgSales / Sub2.SalesCount) as AvgSales
from
(
select sum(TotalSum) as AvgSales
from (
select sum(ListPrice) TotalSum
from tblOrderSales
where [listprice] > 0
group by [Shop ID]
)as subsub1) as sub1,
(
select count(TotalSum) as SalesCount
from (
select sum(ListPrice) TotalSum
from tblOrderSales
where [listprice] > 0
group by [Shop ID]
)as subsub2
) as sub2

Same Results:
With View
(1 row(s) affected) - 5452283

Without View
(1 row(s) affected) - 5452283

Works great, thanks to this link:
http://weblogs.asp.net/jgalloway/archive/2004/05/19/135358.aspx for showing me to use a derived table!!!

Aggregate First Not Available?

The following query runs on Access and returns a result table that removes
duplicate account numbers even if they have different names.
SELECT AcctNum, First(t.AccountName) AS AccountName
FROM tblAcctChart t
GROUP BY AcctNum
ORDER BY AcctNum;
101 N1
101 N2
101 N3
results in
101 N1
I am feeling real dumb about this. First is not an aggregate function
available on SQL Server 2000. However, I know a way to accomplish the same
thing exists on SQL Server. I am having a serious senior moment and cannot
work my way through the fog to put one together.
Would some kind soul please give me a query that will run on SQL Server
2000 that will accomplish the same task.
Thanks.
MikeThere is no native ordering of rows in a table, so the meaning of "first"
depends on what criteria you use to pick to one AccountName that survives.
To pick the first name alphabetically:
SELECT AcctNum, MIN(AccountName) AS AccountName
FROM tblAccChart
GROUP BY AcctNum
ORDER BY AccNum
"MikeV06" wrote:

> The following query runs on Access and returns a result table that removes
> duplicate account numbers even if they have different names.
> SELECT AcctNum, First(t.AccountName) AS AccountName
> FROM tblAcctChart t
> GROUP BY AcctNum
> ORDER BY AcctNum;
> 101 N1
> 101 N2
> 101 N3
> results in
> 101 N1
> I am feeling real dumb about this. First is not an aggregate function
> available on SQL Server 2000. However, I know a way to accomplish the same
> thing exists on SQL Server. I am having a serious senior moment and cannot
> work my way through the fog to put one together.
> Would some kind soul please give me a query that will run on SQL Server
> 2000 that will accomplish the same task.
> Thanks.
> Mike
>|||You either need to use MIN or TOP, depending on what you want.
MIN(t.AccountName) will give you the first account name, alphabetically for
each AcctNum.
select top 1 from columnList
order by columnList
will return only the first row based on your order by clause.
"MikeV06" <me@.privacy.net> wrote in message
news:1t6yt3o7yvb6w.dlg@.mycomputer06.invalid.com...
> The following query runs on Access and returns a result table that removes
> duplicate account numbers even if they have different names.
> SELECT AcctNum, First(t.AccountName) AS AccountName
> FROM tblAcctChart t
> GROUP BY AcctNum
> ORDER BY AcctNum;
> 101 N1
> 101 N2
> 101 N3
> results in
> 101 N1
> I am feeling real dumb about this. First is not an aggregate function
> available on SQL Server 2000. However, I know a way to accomplish the same
> thing exists on SQL Server. I am having a serious senior moment and cannot
> work my way through the fog to put one together.
> Would some kind soul please give me a query that will run on SQL Server
> 2000 that will accomplish the same task.
> Thanks.
> Mike|||MikeV06 wrote:
> The following query runs on Access and returns a result table that removes
> duplicate account numbers even if they have different names.
> SELECT AcctNum, First(t.AccountName) AS AccountName
> FROM tblAcctChart t
> GROUP BY AcctNum
> ORDER BY AcctNum;
> 101 N1
> 101 N2
> 101 N3
> results in
> 101 N1
> I am feeling real dumb about this. First is not an aggregate function
> available on SQL Server 2000. However, I know a way to accomplish the same
> thing exists on SQL Server. I am having a serious senior moment and cannot
> work my way through the fog to put one together.
> Would some kind soul please give me a query that will run on SQL Server
> 2000 that will accomplish the same task.
> Thanks.
> Mike
Use MIN or MAX or come up with a better definition of what you mean by
"first". The Access query you posted actually returns a random result
for the value of AccountName, which may not be a good idea in many
cases.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||> I am feeling real dumb about this. First is not an aggregate function
> available on SQL Server 2000.
Because "FIRST" does not make sense. First what? Physical row? Tables
are, by definition, an unordered set of rows. So the concepts of FIRST,
LAST, or 23rd row make absolutely no sense unless you give more context. As
others have noted, you can order alphabetically by using the aggregate
function MIN(t.AccountName).
I have a brief bit on this here (about 80% of the way down), but in general
the article may be useful to you as well:
http://www.aspfaq.com/2214
A|||My thanks to all who replied. Of course, Min/Max -- blind spot zapping me
broadside. I was overlooking the obvious and trying to make it difficult.
Arggg... Thanks again.
On Wed, 17 May 2006 13:46:01 -0700, Mark Williams wrote:
> There is no native ordering of rows in a table, so the meaning of "first"
> depends on what criteria you use to pick to one AccountName that survives.
> To pick the first name alphabetically:
> SELECT AcctNum, MIN(AccountName) AS AccountName
> FROM tblAccChart
> GROUP BY AcctNum
> ORDER BY AccNum
> "MikeV06" wrote:
>|||On Wed, 17 May 2006 17:05:46 -0400, Aaron Bertrand [SQL Server MVP] wrote:

> Because "FIRST" does not make sense. First what? Physical row? Tables
> are, by definition, an unordered set of rows. So the concepts of FIRST,
> LAST, or 23rd row make absolutely no sense unless you give more context.
As
> others have noted, you can order alphabetically by using the aggregate
> function MIN(t.AccountName).
> I have a brief bit on this here (about 80% of the way down), but in genera
l
> the article may be useful to you as well:
> http://www.aspfaq.com/2214
> A
Very useful indeed. Thank you for the url.

AGGREGATE doesn't do MIN/MAX on textual columns

Hi,

Can anyone from MS exaplain why the AGGREGATE component doesn't allow you to select MIN/MAX when the column is DT_STR/DT_WSTR?

Thanks

Jamie

Anyone?

|||

Thanks Jamie.

Frankly, it did not seem like a common request for our core data warehousing scenarios - so it was not coded in from day one. During beta, a couple of customers did request it, but they were able to work around the issue using a script component. And, as I remember, becuase they had some additional processing to do once they had found the max string value, a script would have been needed at some point anyway.

Always interested to hear scenarios of course. Meanwhile, this would be an interesting DCR, but so far we have not had much demand.

Donald

However, as my Aunt once said to a salesman who suggested there was "no demand" for something she was seeking - "There is a demand standing right in front of you, young man!"

|||

OK thanks Donald. Sommeone on this forum was indeed asking for it and when he asked why it wasn't there I couldn't answer him. Eventually he used, as you say, a script component.

-Jamie

|||

That someone was me

I am implementing a DW/DM, where I collect data from ca. 25 different source systems / DW's. One case where I need max on varchar: In some cases, as I collect data on invoice row level, one invoice row is allocated to more than one cost center (1-n), and I somehow have to collect only one of the cost centers. The cost center data is in a varchar column, and thus I need some way to collect one of the many choices. For the sake of plausible validation, I always want to take value using same method (max or min, since they are easy to write into sql).. I know the proper way of collecting this kind of data would be at the cost center level, but let's not get into that.

In earlier DW implementations I have made (using Ascential/IBM Datastage), I had tens of cases where I had to take max/min of a varchar column.

It could easily be so that even if the data itself is numeric, it is stored in a varchar column, and I hate strong type casts, since I can never be sure if there could sometimes be text information as the column allows it. I have seen that also.

Not implementing max/min on varchar columns seems as a silly limitation in SSIS. In my opinion, this kind of features should be included in the basic transforms, so that there would be no need to always write short scripts - thats what was used in DTS.

IMHO Datastage / Informatica are much more user friendly than DTS in sense for not needing programming skills, I would really like to see SSIS to evolve more into that direction (which it already has, when comparing to DTS).

Markus

|||If you consider adding min/max aggregation of text values to the aggregate transform, while MS in the code consider adding first and last. Occasionally they are very helpful.|||

Guys,

You're more likely to get this functionality if you ask for it thru the proper channels. Click through here and vote, and add a comment

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=131210

Anecdotes of why you need it are a huge help as well.

-Jamie

|||How about including it because it's valid transact-sql to use it on non-numeric fields?

It'd be better to allow for the aggregate functions to work as they are supposed to according to the MS Transact-SQL documentation. It's also better to use an aggregate transform in my package rather than having to query outside of the datastream to do something that should've been included in the first place.

For a given group of records, I want to, for instance, find the minimum text value in a column -- I shouldn't need a script to tell me that.|||

Phil Brammer wrote:

It'd be better to allow for the aggregate functions to work as they are supposed to according to the MS Transact-SQL documentation.

Why?

SSIS is an ETL-tool, not a relational database. Nowhere in the SSIS documentation does it state that there is any adherance to T-SQL, ANSI SQL or any other SQL dialect.

I agree that the AGGREGATE should allow min/max on string fields but my justification for that is because it is something useful for ETL developers, not because some other development platform supports it.

-Jamie

|||

Jamie Thomson wrote:

Phil Brammer wrote:

It'd be better to allow for the aggregate functions to work as they are supposed to according to the MS Transact-SQL documentation.

Why?

SSIS is an ETL-tool, not a relational database. Nowhere in the SSIS documentation does it state that there is any adherance to T-SQL, ANSI SQL or any other SQL dialect.

I agree that the AGGREGATE should allow min/max on string fields but my justification for that is because it is something useful for ETL developers, not because some other development platform supports it.

-Jamie

Actually, yes it does reference Transact-SQL. Read the MSDN page on aggregate transformations. For each operation in the aggregation, they state to read the Transact-SQL documentation for more information.

Granted, I see the exception on the min/max operations... I'm just providing yet another reason to include the functionality. I can also appreciate why it was left out... It's far easier to calculate min/max on a strictly numeric field, considering you have to do an expensive sort on non-numeric fields to determin min/max.

Phil|||

Phil Brammer wrote:

Jamie Thomson wrote:

Phil Brammer wrote:

It'd be better to allow for the aggregate functions to work as they are supposed to according to the MS Transact-SQL documentation.

Why?

SSIS is an ETL-tool, not a relational database. Nowhere in the SSIS documentation does it state that there is any adherance to T-SQL, ANSI SQL or any other SQL dialect.

I agree that the AGGREGATE should allow min/max on string fields but my justification for that is because it is something useful for ETL developers, not because some other development platform supports it.

-Jamie

Actually, yes it does reference Transact-SQL. Read the MSDN page on aggregate transformations. For each operation in the aggregation, they state to read the Transact-SQL documentation for more information.

Phil Brammer wrote:

Does it? Then I stand corrected.

I personally think that's a bad idea because of the reasons elucidated in my last post in this thread, but hey!

Granted, I see the exception on the min/max operations... I'm just providing yet another reason to include the functionality. I can also appreciate why it was left out... It's far easier to calculate min/max on a strictly numeric field, considering you have to do an expensive sort on non-numeric fields to determin min/max.

Phil

true!

-J

|||

All,

The original feedback item was posted under the wrong category so I've re-posted here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=246223

Thanks to Phil for pointing it out.

-Jamie