Creating columns within query... :0(

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Creating columns within query... :0(

Author

Topic

clearthoughtz
Starting Member

3 Posts

Posted - 2007-04-19 : 19:12:07

I'm having having problems populating multiple tables in the select statement below. I'd add additional tables to this results without having to manually update the query. Currently, the I'm using 'BAH' to filter this particular query results. I have 10 additional names I'd like to have included. They are in a column "[SITE]" within the "vDISA_Wrksta" view.

DECLARE @per_site decimal(4,1);
SET @per_site = CAST((SELECT COUNT([Name])
FROM vDISA_Wrksta wsk
WHERE wsk.[SITE] = 'BAH')AS int)

SELECT SUBSTRING(os.[Patch Id],1,8) AS [Patches],
ROUND(((CAST(COUNT(wk.[Name])AS int)/@per_site)), 2)*100 AS [BAH]

FROM vDISA_Wrksta wk JOIN AeXInv_AeX_OS_Updates os
ON wk.WrkstaId = os.WrkstaId
GROUP BY os.[Patch Id], wk.site
HAVING wk.site = 'BAH'
ORDER BY os.[Patch Id]

CURRENT RESULTS:

Patches | BAH
--------------
KB8888888| 2.0

GOAL:

Patches | BAH | AMN | APN
---------------------------
KB8888888| 2.0 | 5.0 | 75.0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-19 : 20:24:21

[code]
SELECT SUBSTRING(os.[Patch Id],1,8) AS [Patches],
SUM(CASE WHEN wk.site = 'BAH' then ROUND(((CAST(COUNT(wk.[Name])AS int)/@per_site)), 2) * 100 END) AS [BAH]
SUM(CASE WHEN wk.site = 'AMN' then ROUND(((CAST(COUNT(wk.[Name])AS int)/@per_site)), 2) * 100 END) AS [AMN]
SUM(CASE WHEN wk.site = 'APN' then ROUND(((CAST(COUNT(wk.[Name])AS int)/@per_site)), 2) * 100 END) AS [APN]
FROM vDISA_Wrksta wk JOIN AeXInv_AeX_OS_Updates os
ON wk.WrkstaId = os.WrkstaId
GROUP BY SUBSTRING(os.[Patch Id],1,8)
ORDER BY [Patches]
[/code]

clearthoughtz
Starting Member

3 Posts

Posted - 2007-04-20 : 07:26:45

I'm getting the following errors when I added your suggestion to my script:

Server: Msg 130, Level 15, State 1, Line 6
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
Server: Msg 170, Level 15, State 1, Line 7
Line 7: Incorrect syntax near 'CAST'.
Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near 'CAST'.

Do I need to rewrite the Declare statement? Also, BAH, AMN, and ANP are part rows within the wsk.[SITE] column. There are additional sites there. How would I write the query to pull whatever rows are listed there and give me the listing without having to manually write 10 additional CASE functions. I'm trying to prevent having to make changes to this query if sites are added or subtracted from the wsk.[SITE] column. Thanks for your help...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-20 : 23:57:22

[code]
SELECT SUBSTRING(os.[Patch Id],1,8) AS [Patches],
SUM(CASE WHEN wk.site = 'BAH' THEN ROUND(COUNT(wk.[Name]) * 100.0 / @per_site, 2) END) AS [BAH],
SUM(CASE WHEN wk.site = 'AMN' THEN ROUND(COUNT(wk.[Name]) * 100.0 / @per_site, 2) END) AS [AMN],
SUM(CASE WHEN wk.site = 'APN' THEN ROUND(COUNT(wk.[Name]) * 100.0 / @per_site, 2) END) AS [APN]
FROM vDISA_Wrksta wk JOIN AeXInv_AeX_OS_Updates os
ON wk.WrkstaId = os.WrkstaId
GROUP BY SUBSTRING(os.[Patch Id],1,8)
ORDER BY [Patches]
[/code]

"Do I need to rewrite the Declare statement?"
YES.

"Also, BAH, AMN, and ANP are part rows within the wsk.[SITE] column. There are additional sites there. How would I write the query to pull whatever rows are listed there and give me the listing without having to manually write 10 additional CASE functions"
You have to manually write the 10 additional CASE fucntion if you want to do it in T-SQL. The easiest way to do is don't do this in T-SQL at all. Use your front end reporting tool to do this.

clearthoughtz
Starting Member

3 Posts

Posted - 2007-04-24 : 12:47:52

Thank you for you help... You been a great help. I redid the query and added your suggestions. I'm not getting needed results. If I type a site in the "HAVING" clause, I get the results for that site but there others come back with a null result. Right now "BAH" is specified in the "HAVING" clause and so I get the required result for that table and not the others. I'm not sure how I should write the having clause.

DECLARE @per_site_anm varchar(10);
SET @per_site_anm = CAST((SELECT COUNT([Name])FROM vDISA_Wrksta wsk WHERE wsk.[SITE] = 'ANM')AS int)
DECLARE @per_site_anp varchar(10);
SET @per_site_anp = CAST((SELECT COUNT([Name])FROM vDISA_Wrksta wsk WHERE wsk.[SITE] = 'ANP')AS int)
DECLARE @per_site_bah varchar(10);
SET @per_site_bah = CAST((SELECT COUNT([Name])FROM vDISA_Wrksta wsk WHERE wsk.[SITE] = 'BAH')AS int)

SELECT SUBSTRING(os.[Patch Id],1,8) AS [Patches],

(CASE WHEN wk.site = 'ANM' THEN COUNT(wk.[Name]) * 100 / @per_site_anm END) AS [ANM],
(CASE WHEN wk.site = 'ANP' THEN COUNT(wk.[Name]) * 100 / @per_site_anp END) AS [ANP],
(CASE WHEN wk.site = 'BAH' THEN COUNT(wk.[Name]) * 100 / @per_site_bah END) AS [BAH]

FROM vDISA_Wrksta wk JOIN AeXInv_AeX_OS_Updates os
ON wk.WrkstaId = os.WrkstaId
GROUP BY os.[Patch Id], wk.[SITE]
HAVING wk.[site] = 'BAH'

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-04-24 : 18:27:00

remove the having clause


FROM vDISA_Wrksta wk JOIN AeXInv_AeX_OS_Updates os
ON wk.WrkstaId = os.WrkstaId
GROUP BY os.[Patch Id], wk.[SITE]
HAVING wk.[site] = 'BAH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources