sql aggregate - perhaps - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

sql aggregate - perhaps

Author

Topic

pithhelmet
Posting Yak Master

183 Posts

Posted - 2007-02-27 : 09:26:22

Hi everyone -

I'm not sure if this is an aggregate or not, but here is the table
source table and the results that need to be shown.

ID | CREW_TYPE | QTY | STATUS | BP | BC |
-----------------------------------------------------
1 1 2 2 [null] 100
2 2 3 1 [null] 100
3 3 4 4 [null] 100
4 1 1 1 101 [null]
5 2 1 1 101 [null]
6 2 5 2 [null] 100
7 2 1 3 [null] 100

The returned information from the query is....

CREW_TYPE | QTY | S1 | S2 | S3 | S4 | S5 |
----------------------------------------------------
1 2 0 2 0 0 0
2 9 3 5 1 0 0
3 4 0 0 0 4 0

If someone would be so kind as to help me with the query
that would be used to return the results as displayed.

thanks
tony

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-27 : 09:31:19

You have 6 columns in the table but the sample data only show 5.

What is S1, S2 etc ?

pithhelmet
Posting Yak Master

183 Posts

Posted - 2007-02-27 : 09:47:22

s1 - s5 is from the status column

sorry about that

i'll try to reform the grid as well

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-27 : 09:53:14

Please also explain how the result is obtain from the sample data and make sure the result matches the sample data provided

pithhelmet
Posting Yak Master

183 Posts

Posted - 2007-02-27 : 10:03:36

The grid reformat doesn't hold in the display - so thats out....

The results are what i would want it to look like,
and that is where i need help with.

Basically, i think there needs to be a selection on
the field BC where BC = 100

Then a quantity would need to be calculated for each of the
distinct crew_type.

And each of the status would be grouped????

thanks
tony

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-27 : 10:15:57

[code]
declare @table table
(
[ID] int,
CREW_TYPE int,
QTY int,
STATUS int,
BP int,
BC int
)
insert into @table
select 1, 1, 2, 2, null, 100 union all
select 2, 2, 3, 1, null, 100 union all
select 3, 3, 4, 4, null, 100 union all
select 4, 1, 1, 1, 101, null union all
select 5, 2, 1, 1, 101, null union all
select 6, 2, 5, 2, null, 100 union all
select 7, 2, 1, 3, null, 100

select CREW_TYPE,
QTY = sum(QTY),
S1 = sum(case when STATUS = 1 then QTY else 0 end),
S2 = sum(case when STATUS = 2 then QTY else 0 end),
S3 = sum(case when STATUS = 3 then QTY else 0 end),
S4 = sum(case when STATUS = 4 then QTY else 0 end),
S5 = sum(case when STATUS = 5 then QTY else 0 end)
from @table
where BC = 100
group by CREW_TYPE

/*
CREW_TYPE QTY S1 S2 S3 S4 S5
----------- ----------- ----------- ----------- ----------- ----------- -----------
1 2 0 2 0 0 0
2 9 3 5 1 0 0
3 4 0 0 0 4 0
*/
[/code]

pithhelmet
Posting Yak Master

183 Posts

Posted - 2007-02-27 : 10:26:46

100% right on the money!!!

two additional questions.....

1) Is this called an aggregate?
2) Is there a way to extend the query to perform sum
on a disctict query to get the status??

The second question is forward thinking that there could be
additional status's

thanks for all the help

take care
tony

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-27 : 10:30:46

1. Yes. sum() is aggregate
2. what do you have in mind ?

pithhelmet
Posting Yak Master

183 Posts

Posted - 2007-02-27 : 10:56:13

In case the status table is expanded (more status types added)
i wouldn't have to perform maintenance on the query.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-27 : 19:06:43

Yes. You will have to change the query.