Combine multiple queries in 1 return row

Question

I have a query which should return 1 row for each DetectorID. What we currently have is the following:

DECLARE @StartDate datetime SET @StartDate  = '2017-12-01 00:00'
DECLARE @EndDate datetime select @EndDate ='2018-01-01 00:00'

DECLARE @Limit1 real SET @Limit1 = 200; DECLARE @AssenLimit1 real SET @AssenLimit1 = 0;
DECLARE @Limit2 real SET @Limit2 = 300; DECLARE @AssenLimit2 real SET @AssenLimit2 = 0;
DECLARE @Limit3 real SET @Limit3 = 350; DECLARE @AssenLimit3 real SET @AssenLimit3 = 0;
DECLARE @Limit4 real SET @Limit4 = 400; DECLARE @AssenLimit4 real SET @AssenLimit4 = 0;
DECLARE @Limit5 real set @Limit5 = 500; DECLARE @AssenLimit5 real SET @AssenLimit5 = 0; 

SELECT DetectorID,  AVG(ValidWIM) AS 'PI_mean_WIM',
COUNT(ValidWIM) AS 'PI_mean_WIM2',
COUNT(CASE
        WHEN ValidWIM > 0.5 THEN ValidWIM ELSE NULL END)
        AS 'PI_mean_WIM3'

,AVG((ValidWDD_Left+ValidWDD_Right)/2) AS 'PI_mean_WDD' 
,COUNT(ValidWDD_Left) AS 'PI_mean_WDD2'
,COUNT (CASE
        WHEN (ValidWDD_Left>0.5 AND ValidWDD_Right>0.5) THEN ValidWDD_Left ELSE NULL END)
        AS PI_mean_WDD3
,COUNT(CASE 
        WHEN AxleLoad IS NOT NULL Then TrainPassageInformationID ELSE NULL END)
         AS 'nWheels' 
, COUNT(CASE
        WHEN (PeakForceLeft > @Limit1 OR PeakForceRight>@Limit1) THEN TrainPassageInformationID ELSE NULL END)
        AS 'n>200'
, COUNT(CASE
        WHEN (PeakForceLeft > @Limit2 OR PeakForceRight>@Limit2) THEN TrainPassageInformationID ELSE NULL END)
        AS 'n>300'
, COUNT(CASE
        WHEN (PeakForceLeft > @Limit3 OR PeakForceRight>@Limit3) THEN TrainPassageInformationID ELSE NULL END)
        AS 'n>350'
, COUNT(CASE
        WHEN (PeakForceLeft > @Limit4 OR PeakForceRight>@Limit4) THEN TrainPassageInformationID ELSE NULL END)
        AS 'n>400'
, COUNT(CASE
        WHEN (PeakForceLeft > @Limit5 OR PeakForceRight>@Limit5) THEN TrainPassageInformationID ELSE NULL END)
        AS 'n>500'
From WheelDamage 
where (TimeOfAxle > @StartDate AND TimeOfAxle < @EndDate) and DetectorID in (11,12)
GROUP BY DetectorID 
order by DetectorID asc
SELECT DetectorID,
AVG(PeakForceLeft) AS 'NoiseLeft' FROM WheelDamage as t
where PeakForceLeft in (
    select top 10 percent PeakForceLeft 
    from wheeldamage as tt
    where tt.WheelDamageID = t.WheelDamageID AND tt.PeakForceLeft <> 0  AND tt.PeakForceLeft  IS NOT NULL AND (t.TimeOfAxle > @StartDate AND t.TimeOfAxle < @EndDate)
    order by tt.PeakForceLeft asc)
Group By DetectorID 
order by DetectorID asc


SELECT DetectorID,
AVG(PeakForceRight) AS 'NoiseRight' FROM WheelDamage as t
where PeakForceLeft in (
    select top 10 percent PeakForceRight
    from wheeldamage as tt
    where tt.WheelDamageID = t.WheelDamageID AND tt.PeakForceRight <> 0  AND tt.PeakForceLeft  IS NOT NULL AND (t.TimeOfAxle > @StartDate AND t.TimeOfAxle < @EndDate)
    order by tt.PeakForceLeft asc)
    Group By DetectorID 
order by DetectorID asc

SELECT DetectorId, COUNT (DateTime) AS 'Tags1' 
FROM [TagPassage] 
WHERE  Valid = 1 AND (DateTime > @StartDate AND DateTime < @EndDate)
GROUP by DetectorID 
Order by DetectorID asc

SELECT DetectorID, COUNT(CASE 
    WHEN (TotalWeight=0 OR TotalWeight IS NULL) AND (HasTrainStandStill<>1 OR HasTrainStandStill IS NULL) THEN  (TrainPassageInformationID) ELSE NULL END) AS 'notAnalyzed'
 , SUM (TotalWeight) AS 'TotalWeight'
FROM TrainPassageInformation 
WHERE   (Datetime > @StartDate AND Datetime < @EndDate) 
Group By DetectorID 
order by DetectorID asc

This is returing 5 new rows which I understand. This is because i have 5 select statements. However, I need to know how i can put the bottom 4 select statements in the top one so that it will only return 1 row.

I have tried with Select(Select X xxx). But that gave the error that it is returning more then 1 rows.

The image above is showing the current result. I want the bottom 4 tables to be columns in the first table.

I would seriously consider simplifying your problem to an example that people will want to read and possibly be able to run locally, nobody wants to read and try to figure out a big long SQL statement. You're much more likely to get an answer that way. — Tanner, Jan 31 '18 at 10:19
take a look [at this answer for an example of how you can create a rerunnable script](https://stackoverflow.com/a/48445359/57475) and consider adding a sample that illustrates your problem and desired output. — Tanner, Jan 31 '18 at 10:22

score 0 · Answer 1 · answered Jan 31 '18 at 10:31

0

can you just join/left join all the scripts together as table and link by DetectorID, then u can select the columns from the 4 scripts at the bottom to the right.

answered Jan 31 '18 at 10:31

Wince

21
3

Combine multiple queries in 1 return row

1 Answers1