Can't get it to work in one query

[ruscan]

Hello,

I have a little issue getting what I want displayed from a single table.
Basically, here is what I am trying to accomplish. I have the following type of table:

Place Action B/S Qty Type
City Full B 1 View
City Partial B 1 View
City Partial B 1 View
City Partial B 2 View
City Full B 1 View
City Full S 3 View
City Full S 3 View
Town Partial B 1 View
Town Full B 1 View
Town Partial S 1 View
Town Full S 1 View

I am trying to have my query display the following rows
City 6 (6 is the Sum of Qty for B/S=B)
City 6 (6 is the Sum of Qty for B/S=S)
Town 2 (2 is the Sum of Qty for B/S=B)
Town 2 (2 is the Sum of Qty for B/S=S)

If anyone knows how to accomplish this in one query, that would be great.

Thank you in advance!
Anton

[pclfw]

A thought first of all, your example data doesn't match your example results. So actually no one could produce the correct results from this data.

However, build and test each separate row of the required result set separately. Then join then with a UNION clause.

[Owais_Khan]

Good Yar

[unleashedmaniac]

Hello,

I have a little issue getting what I want displayed from a single table.
Basically, here is what I am trying to accomplish. I have the following type of table:

Place Action B/S Qty Type
City Full B 1 View
City Partial B 1 View
City Partial B 1 View
City Partial B 2 View
City Full B 1 View
City Full S 3 View
City Full S 3 View
Town Partial B 1 View
Town Full B 1 View
Town Partial S 1 View
Town Full S 1 View

I am trying to have my query display the following rows
City 6 (6 is the Sum of Qty for B/S=B)
City 6 (6 is the Sum of Qty for B/S=S)
Town 2 (2 is the Sum of Qty for B/S=B)
Town 2 (2 is the Sum of Qty for B/S=S)

If anyone knows how to accomplish this in one query, that would be great.

Thank you in advance!
Anton

I believe this is what you are looking for...

--Creating a temp table to query.
create table #foo([Place] varchar(10),
                  [Action] varchar(10),
                  [B/S] char(1),
                  [Qty] int,
                  [Type] varchar(4))

-- Inserting your sample data into the temp table
insert into #foo([Place],[Action],[B/S],[Qty],[Type])
         values ('City','Full','B',1,'View')

insert into #foo([Place],[Action],[B/S],[Qty],[Type])
         values ('City','Partial','B',1,'View')

insert into #foo([Place],[Action],[B/S],[Qty],[Type])
         values ('City','Partial','B',1,'View')                 

insert into #foo([Place],[Action],[B/S],[Qty],[Type])
         values ('City','Partial','B',2,'View')          

insert into #foo([Place],[Action],[B/S],[Qty],[Type])
         values ('City','Full','B',1,'View')          

insert into #foo([Place],[Action],[B/S],[Qty],[Type])
         values ('City','Full','S',3,'View')          

insert into #foo([Place],[Action],[B/S],[Qty],[Type])
         values ('City','Full','S',3,'View')
          
insert into #foo([Place],[Action],[B/S],[Qty],[Type])
         values ('Town','Partial','B',1,'View')

insert into #foo([Place],[Action],[B/S],[Qty],[Type])
         values ('Town','Full','B',1,'View')          

insert into #foo([Place],[Action],[B/S],[Qty],[Type])
         values ('Town','Partial','S',1,'View')

insert into #foo([Place],[Action],[B/S],[Qty],[Type])
         values ('Town','Full','S',1,'View')


-- Don't know how you want the output, but here is a few samples...          
Select Place, SUM(Qty) as Total 
  from #foo
 group by Place, [B/S]
 Order by Place

Select Place, SUM(Qty) as Total, [B/S]
  from #foo
 group by Place, [B/S]
 Order by Place

Select Place + ' ' + Convert(varchar(4),SUM(Qty)) as MyColumn
  from #foo
 group by Place, [B/S]
 Order by Place


Select Place + ' ' + Convert(varchar(4),SUM(Qty)) + 
         ' (' + Convert(varchar(4),SUM(Qty)) + ' is the Sum of Qty for B/S=' + 
         [B/S] + ')' 
       as MyColumn
  from #foo
 group by Place, [B/S]
 Order by Place

drop table #foo

Gives me:

Place	Total
------------------------
City	6
City	6
Town	2
Town	2


Place	Total	B/S
------------------------
City	6	B
City	6	S
Town	2	B
Town	2	S


MyColumn
------------------------
City 6
City 6
Town 2
Town 2


MyColumn
----------------------------------------
City 6 (6 is the Sum of Qty for B/S=B)
City 6 (6 is the Sum of Qty for B/S=S)
Town 2 (2 is the Sum of Qty for B/S=B)
Town 2 (2 is the Sum of Qty for B/S=S)