views:

302

answers:

3

hello, I have a table that contains information for 4 electrical generators I would like to have the results of the four querys in one row. does any one have a suggestion. Thanks

SELECT  avg(KW) as GEN_101_AVG
FROM         genset WHERE     (GenSetName   like 'GEA3519') and GenDate >= '1 jan   2003    00:00:00' and GenDate < '1 feb 2003 00:00:00'

   SELECT  avg(KW) as GEN_201_AVG
FROM         genset WHERE     (GenSetName   like 'GEA3520') and GenDate >= '1 jan 2003 00:00:00' and GenDate < '1 feb 2003 00:00:00'

SELECT  avg(KW) as GEN_301_AVG
FROM         genset WHERE     (GenSetName   like 'GEA3521') and GenDate >= '1 jan 2003 00:00:00' and GenDate < '1 feb 2003 00:00:00'

SELECT  avg(KW) as GEN_401_AVG
FROM         genset WHERE     (GenSetName   like 'GEA3522') and GenDate >= '1 jan 2003 00:00:00' and GenDate < '1 feb 2003 00:00:00'

MS-SQL

+12  A: 
SELECT  (
        SELECT  avg(KW)
        FROM    genset
        WHERE   (GenSetName   like 'GEA3519')
                and GenDate >= '1 jan   2003    00:00:00'
                and GenDate < '1 feb 2003 00:00:00'
        ) AS avg_GEA3519,
        (
        SELECT  avg(KW)
        FROM    genset
        WHERE   (GenSetName   like 'GEA3520')
                and GenDate >= '1 jan   2003    00:00:00'
                and GenDate < '1 feb 2003 00:00:00'
        ) AS avg_GEA3520,
        (
        SELECT  avg(KW)
        FROM    genset
        WHERE   (GenSetName   like 'GEA3521')
                and GenDate >= '1 jan   2003    00:00:00'
                and GenDate < '1 feb 2003 00:00:00'
        ) AS avg_GEA3521,
        (
        SELECT  avg(KW)
        FROM    genset
        WHERE   (GenSetName   like 'GEA3522')
                and GenDate >= '1 jan   2003    00:00:00'
                and GenDate < '1 feb 2003 00:00:00'
        ) AS avg_GEA3522

, or in SQL Server 2005+, this:

SELECT  [GEA3519], [GEA3520], [GEA3521], [GEA3522]
FROM    (
        SELECT  GenSetName, KW
        FROM    genset
        WHERE   GenDate >= '1 Jan 2003  00:00:00'
                AND GenDate < '1 Feb 2003 00:00:00'
        ) AS q
PIVOT
(
        AVG(KW)
        FOR  GenSetName IN (['GEA3519'], ['GEA3520'], ['GEA3521'], ['GEA3522']
)
Quassnoi
My thoughts entirely- though I would personally alias those columns too.
RichardOD
Well, that's good too :-) I was a little late posting my variable approach.
Thorsten Dittmar
+1  A: 

As the number of selects is limited to four, one solution would be to select into variables and then do a final select. Like the following:

declare @var1 <TYPE>
declare @var2 <TYPE>
declare @var3 <TYPE>
declare @var4 <TYPE>

select @var1 = SELECT avg(...) ...
select @var2 = ...
select @var3 = ...
select @var4 = ...

select @var1 as ..., @var2 as ..., @var3 as ..., @var4 as ...
Thorsten Dittmar
you might better go with the above subquery solution. You may use my approach in case you need to manipulate the four values before selecting.
Thorsten Dittmar
+2  A: 

Another option:

SELECT
     AVG(GEN_101.kw) AS GEN_101_AVG,
     AVG(GEN_201.kw) AS GEN_201_AVG,
     AVG(GEN_301.kw) AS GEN_301_AVG,
     AVG(GEN_401.kw) AS GEN_401_AVG
FROM
     Genset GEN_101
INNER JOIN Genset GEN_201 ON
     GEN_201.GenSetName = 'GEA3520' AND
     GEN_201.GenDate >= '1 jan 2003 00:00:00' AND
     GEN_201.GenDate < '1 feb 2003 00:00:00'
INNER JOIN Genset GEN_101 ON
     GEN_301.GenSetName = 'GEA3521' AND
     GEN_301.GenDate >= '1 jan 2003 00:00:00' AND
     GEN_301.GenDate < '1 feb 2003 00:00:00'
INNER JOIN Genset GEN_101 ON
     GEN_401.GenSetName = 'GEA3522' AND
     GEN_401.GenDate >= '1 jan 2003 00:00:00' AND
     GEN_401.GenDate < '1 feb 2003 00:00:00'
WHERE
     GEN_101.GenSetName = 'GEA3519' AND
     GEN_101.GenDate >= '1 jan 2003 00:00:00' AND
     GEN_101.GenDate < '1 feb 2003 00:00:00'
Tom H.
This will indeed work for AVG (and only for AVG), since AVG does not depend on how many times the table is being cross-joined to another table. Performance will of course be poor, but a working and not so obvious solution deserves a +1.
Quassnoi