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.

Anton

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.

Good Yar

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.

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)``````
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.19 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.