Struggling to perfect a SQL statement

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

General SQL Server Forums

New to SQL Server Programming

Struggling to perfect a SQL statement

Author

Topic

tiffanysml
Starting Member

4 Posts

Posted - 2012-08-24 : 22:25:24

Hi I wanted to be able to get totals and also group together some results.

Basically there are two tables.
TABLE 1: building
fields : name, buildingId

TABLE 2: furniture
fields : buildingId, status, condition, cost_replace, comments
status can be ('in', 'out')
condition can be ('new','fair','poor','failed')


My current code is showing this kind of result:
name        statusCond   CountT    ReplaceValue  OverallComments
building1   in - new      5        2000.00       NULL
building1   in - fair     2        1000.00       NULL
building1   in - poor     1        3000.00       1 Handles are stuck
building1   out - failed  2        4000.00       1 No legs, 1 Seat broken
building2   in - fair     10       1000.00       NULL
building2   in - new      5        700.00        NULL

What I am hoping to achieve is to combine the new and fair condition
together and call it 'good' and potentially a rollup:


name        statusCond   CountT    ReplaceValue  OverallComments
building1   in - good      7        3000.00       NULL
building1   in - poor      1        3000.00       1 Handles are stuck
building1   out - failed   2        4000.00       1 No legs, 1 Seat broken
building1   ALL            10       10000.00      NULL
building2   in - good      15       1700.00       NULL 
building2   ALL            15       1700.00       NULL

This is the code I have. Any help would be greatly appreciated!

select 
     name = b.name, 
        statusCond,
        countT = countTotal,
      ReplaceValue = TotalDollar,
		OverallComments
from  furniture f , building b
Join
(     
SELECT  [buildingId], 
	statusCond = status + '- ' + condition,
	countTotal = count(*), 
	TotalDollar = sum(cost_replace),
	STUFF((
    SELECT ', 1 ' + cast([comments] AS VARCHAR(MAX))
    FROM furniture 
		WHERE (buildingId = f.buildingId and status = f.status 
		and condition = f.condition and type = 'chairs') 
		FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,2,'') as OverallComments
FROM    furniture.f
GROUP BY buildingId, status, condition with rollup
) result
on b.buildingId = result.buildingId 
where f.type = 'chairs'
group by b.name, statusCond,countTotal,TotalDollar, OverallComments 
order by b.name, statusCond

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-24 : 22:31:36

[code]
select
name = b.name,
statusCond,
countT = countTotal,
ReplaceValue = TotalDollar,
OverallComments
from furniture f , building b
Join
(
SELECT [buildingId],
statusCond = status + '- ' + case when condition in ('new','fair') then 'good' else condition end
countTotal = count(*),
TotalDollar = sum(cost_replace),
STUFF((
SELECT ', 1 ' + cast([comments] AS VARCHAR(MAX))
FROM furniture
WHERE (buildingId = f.buildingId and status = f.status
and condition = f.condition and type = 'chairs')
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,2,'') as OverallComments
FROM furniture.f
GROUP BY buildingId, status, case when condition in ('new','fair') then 'good' else condition end with rollup
) result
on b.buildingId = result.buildingId
where f.type = 'chairs'
group by b.name, statusCond,countTotal,TotalDollar, OverallComments
order by b.name, statusCond
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tiffanysml
Starting Member

4 Posts

Posted - 2012-08-24 : 23:06:33

Wow thank you for your quick reply, visakh16!

I realized I made a typo in my code for you when I wrote furniture.f I meant furniture f. I ran the code but there it keeps saying

quote:
Column 'furniture.condition' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

But with a slight alteration I could pull the data without a rollup but the comments do not show up anymore. Is there something wrong in this last portion where it says :

GROUP BY buildingId, status, case when condition in ('new','fair') then 'good' else condition end with rollup

Thanks again for anyone looking at this!


select 
     name = b.name, 
        statusCond,
        countT = countTotal,
      ReplaceValue = TotalDollar,
		OverallComments
from  furniture f , building b
Join
(     
SELECT  [buildingId], 
	statusCond = status + '- ' + case when condition in ('new','fair') then 'good' else condition end
	countTotal = count(*), 
	TotalDollar = sum(cost_replace),
	STUFF((
    SELECT ', 1 ' + cast([comments] AS VARCHAR(MAX))
    FROM furniture 
		WHERE (buildingId = f.buildingId and status = f.status 
		and condition = f.condition and type = 'chairs') 
		FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,2,'') as OverallComments
FROM    furniture f
GROUP BY buildingId, status, case when condition in ('new','fair') then 'good' else condition end with rollup
) result
on b.buildingId = result.buildingId 
where f.type = 'chairs'
group by b.name, statusCond,countTotal,TotalDollar, OverallComments 
order by b.name, statusCond

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-24 : 23:10:10

[code]
select
name = b.name,
statusCond,
countT = countTotal,
ReplaceValue = TotalDollar,
OverallComments
from furniture f , building b
Join
(
SELECT [buildingId],
statusCond = status + '- ' + case when condition in ('new','fair') then 'good' else condition end
countTotal = count(*),
TotalDollar = sum(cost_replace),
STUFF((
SELECT ', 1 ' + cast([comments] AS VARCHAR(MAX))
FROM furniture
WHERE (buildingId = f.buildingId and status = f.status
and case when condition in ('new','fair') then 'good' else condition end = case when f.condition in ('new','fair') then 'good' else f.condition end and type = 'chairs')
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,2,'') as OverallComments
FROM furniture f
GROUP BY buildingId, status, case when condition in ('new','fair') then 'good' else condition end with rollup
) result
on b.buildingId = result.buildingId
where f.type = 'chairs'
group by b.name, statusCond,countTotal,TotalDollar, OverallComments
order by b.name, statusCond
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tiffanysml
Starting Member

4 Posts

Posted - 2012-08-24 : 23:56:33

Gosh thanks again...it's so close. And please tell me you get paid to do this on this forum.

The only thing is that is spewing a result like this.



name        statusCond   CountT    ReplaceValue  OverallComments
building1   in - good      7        3000.00       NULL
building1   in - poor      1        3000.00       1 Handles are stuck
building1   out - failed   2        4000.00       1 No legs, 1 Seat broken
building1   ALL            8        10000.00      NULL
building1   ALL            2        10000.00      NULL
building1   ALL            10       10000.00      NULL
building2   in - good      15       1700.00       NULL 
building2   ALL            15       1700.00       NULL

So the three ALL from building 1 is grouping the 'in - good' and 'in - poor' and then separately counting the 'out - failed' and then a full total. Is there a way to filter out those two totals?

If it's a lot of work then we can leave it as is. Because you've already helped me out a lot!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-25 : 13:20:03

[code]
select
name = b.name,
statusCond,
countT = countTotal,
ReplaceValue = TotalDollar,
OverallComments
from furniture f , building b
Join
(
SELECT [buildingId],
statusCond = status + '- ' + case when condition in ('new','fair') then 'good' else condition end
countTotal = count(*),
TotalDollar = sum(cost_replace),
STUFF((
SELECT ', 1 ' + cast([comments] AS VARCHAR(MAX))
FROM furniture
WHERE (buildingId = f.buildingId and status = f.status
and case when condition in ('new','fair') then 'good' else condition end = case when f.condition in ('new','fair') then 'good' else f.condition end and type = 'chairs')
FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)'),1,2,'') as OverallComments
FROM furniture f
GROUP BY buildingId, status + '- ' + case when condition in ('new','fair') then 'good' else condition end with rollup
) result
on b.buildingId = result.buildingId
where f.type = 'chairs'
group by b.name, statusCond,countTotal,TotalDollar, OverallComments
order by b.name, statusCond
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tiffanysml
Starting Member

4 Posts

Posted - 2012-08-25 : 21:50:33

Gosh that last part was actually a simple fix and thanks for showing me now I just learned how to far I can go with the group by statement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-25 : 21:54:17

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources