August 7, 2006 at 9:19 am
Hi all,
I’m writing a query that involves a number of inner joins and aggregate functions.
The results I get are good but not exactly what I need and I’m having difficulty getting my head around how to achieve getting the necessary information.
Here is an example of the current results returned
Number | Value | Location | Order |
4444 | 25 | Room A | 2 |
4444 | 25 | Room B | 4 |
4444 | 25 | Room C | 5 |
4444 | 25 | Room D | 7 |
4444 | 25 | OTHER | 8 |
8888 | 11 | Room C | 5 |
8888 | 11 | OTHER | 8 |
5555 | 18 | Room B | 3 |
5555 | 18 | Room D | 7 |
5555 | 18 | OTHER | 8 |
7777 | 14 | Room A | 2 |
What I’m looking for is something like this
Number | Value | Location | Order |
4444 | 25 | Room A | 2 |
8888 | 11 | Room C | 5 |
5555 | 18 | Room B | 3 |
7777 | 14 | Room A | 2 |
So I can eventually get something like this:-
Location | Value |
Room A | 39 |
Room B | 18 |
Room C | 11 |
Any help on how I should structure the query even if it’s just to get me past the current results I’m getting would be much appreciated.
Many thanks
August 7, 2006 at 11:55 am
Hope this works for you... atleast for the first result set...
Could add more description to your question...
set nocount on
declare @Order table
(
Number int,
[Value] int,
Location varchar(10),
[Order] int
)
insert into @Order values (4444, 25, 'Room A', 2)
insert into @Order values (4444, 25, 'Room B',4)
insert into @Order values (4444, 25, 'Room C',5)
insert into @Order values (4444, 25, 'Room D',7)
insert into @Order values (4444, 25, 'OTHER', 8)
insert into @Order values (8888, 11, 'Room C',5)
insert into @Order values (8888, 11, 'OTHER', 8)
insert into @Order values (5555, 18, 'Room B', 3)
insert into @Order values (5555, 18, 'Room D', 7)
insert into @Order values (5555, 18, 'OTHER', 8)
insert into @Order values (7777, 14, 'Room A', 2)
select a.* from @Order a JOIN (select Number,Min([Order]) MinOrder from
@Order group by Number) as b
on a.Number = b.Number
and a.[Order] = b.MinOrder
where Exists (
where Number = a.Number
and [Order] = a.[Order]
group by Number
)
August 7, 2006 at 12:03 pm
Here is another way that does include your desired result.
DECLARE @TempTable TABLE (Number int,
Value int,
Location varchar(100),
[Order] int)
SET NOCOUNT ON
INSERT INTO @TempTable
SELECT 4444, 25, 'Room A', 2
UNION ALL
SELECT 4444, 25, 'Room B', 4
UNION ALL
SELECT 4444, 25, 'Room C', 5
UNION ALL
SELECT 4444, 25, 'Room D', 7
UNION ALL
SELECT 4444, 25, 'OTHER', 8
UNION ALL
SELECT 8888, 11, 'Room C', 5
UNION ALL
SELECT 8888, 11, 'OTHER', 8
UNION ALL
SELECT 5555, 18, 'Room B', 3
UNION ALL
SELECT 5555, 18, 'Room D', 7
UNION ALL
SELECT 5555, 18, 'OTHER', 8
UNION ALL
SELECT 7777, 14, 'Room A', 2
-- This gets you the intermediate results
SELECT b.Number
, b.Value
, a.Location
, b.[Order]
FROM @TempTable AS a
JOIN (SELECT Number
, Value
, MIN([Order]) AS [Order]
FROM @TempTable
GROUP BY Number, Value) AS b
ON a.Number = b.Number
AND a.Value = b.Value
AND a.[Order] = b.[Order]
-- This would get you the final result
SELECT a.Location
, SUM(b.Value) AS Value
FROM @TempTable AS a
JOIN (SELECT Number
, Value
, MIN([Order]) AS [Order]
FROM @TempTable
GROUP BY Number, Value) AS b
ON a.Number = b.Number
AND a.Value = b.Value
AND a.[Order] = b.[Order]
GROUP BY a.Location
SET NOCOUNT OFF
August 8, 2006 at 2:31 am
cheers guys that seems to do it!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply