0

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

4
Contributors
3
Replies
4
Views
7 Years
Discussion Span
Last Post by unleashedmaniac
0

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.

0

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)

Edited by unleashedmaniac: n/a

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.