January 4, 2006 at 2:57 am
I’m a bit new to SQL and have the following problem to solve and don’t know how to do it. Could someone help me?
There are two tables.
tbl_1:
keyid1 paramlistid paramid specid specversionid
060001 AAA XX AX 1
060002 AAA XX AX 1
060003 AAA YY AX 1
060004 AAA XX AX 2
060005 BBB ZZ AZ 1
tbl_2:
specid specversionid paramlistid paramid ltsequence value
AX 1 AAA XX 1 1
AX 1 AAA XX 2 3
AX 1 AAA XX 3 5
AX 1 AAA XX 4 7
AX 1 AAA XX 5 9
AX 1 AAA YY 1 21
AX 1 AAA YY 2 23
AX 1 AAA YY 3 25
AX 1 AAA YY 4 27
AX 1 AAA YY 5 29
AX 2 AAA XX 1 2
AX 2 AAA XX 2 4
AX 2 AAA XX 3 6
AX 2 AAA XX 4 8
AX 2 AAA XX 5 10
AX 2 AAA YY 1 22
AX 2 AAA YY 2 24
AX 2 AAA YY 3 26
AX 2 AAA YY 4 28
AX 2 AAA YY 5 30
When I do a select on paramlistid=AAA and paramid=XX and specid=AX, the outcome has to be as follows:
keyid1 min3s min2s norm plus2s plus3s
060001 1 3 5 7 9
060002 1 3 5 7 9
060004 2 4 6 8 10
If it's possible, how do I do it?
January 4, 2006 at 1:55 pm
this is a possible solution
SELECT keyid1
, sum (min3s) as min3s
, sum (min2s) as min2s
, sum (norm) as norm
, sum (plus2s) as plus2s
, sum (plus3s) as plus3s
FROM (
SELECT keyid1
, case WHEN ltsequence = 1 then [value] else 0 END as min3s
, case WHEN ltsequence = 2 then [value] else 0 END as min2s
, case WHEN ltsequence = 3 then [value] else 0 END as norm
, case WHEN ltsequence = 4 then [value] else 0 END as plus2s
, case WHEN ltsequence = 5 then [value] else 0 END as plus3s
FROM tbl_1
INNER JOIN tbl_2 ON
tbl_1.paramlistid = tbl_2.paramlistid
AND tbl_1.paramid = tbl_2.paramid
AND tbl_1.specid = tbl_2.specid
AND tbl_1.specversionid = tbl_2.specversionid
WHERE tbl_1.paramlistid = 'AAA'
AND tbl_1.paramid = 'XX'
AND tbl_1.specid = 'AX'
) AS P
GROUP BY keyid1
ORDER BY keyid1
January 5, 2006 at 1:14 am
Thanks. I will try this.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply