[Gabriedl23]

Hi I need to create a Query(A) base on another Query(. In QryA, i want to have a DISTINCT count of the product_ID of QryB. Also, in QryA i want to have an Average (AVG) and a Standard Deviation (StDev) of Field Pk-Pk.deviation of QryB BUT i want all of them, not only the distinct one.
I need to put all of this in only one query.
It need to be put in a Group By Beam .

Here is my SQL for now

SELECT [DT_Analysis_Results_Table].[Beam] AS Beam, 'All' AS Location, 'All' AS Channel, Avg([DT_Analysis_Results_Table].[Pk-Pk_deviation(dB)]) AS [Mean of Pk-Pk Deviation], StDev([DT_Analysis_Results_Table].[Pk-Pk_deviation(dB)]) AS [Standard Deviation of Pk-Pk Deviation], Count(*) AS NbrAcquisition
FROM (SELECT DISTINCT  [Product_ID]
FROM [Report_filter_temp]) 
GROUP BY DT_Analysis_Results_Table.[Beam]

The problem is i want the count of different Product_ID and the Stdev and the average of PK-PK_deviation(dB) of all of the records.

Btw the code is not right, im only getting one record while i'm suppos to have 222. I've put it this way so you guys can understand.

I think there's a way to do this in one Query with the JOIN or UNION operator (in one i can do a normal Select while in the other one i can do a select Distinct)

This post has been edited by modi123_1: 06 June 2012 - 08:31 AM
Reason for edit:: dropped 'challenge' from title..

[Gabriedl23]

I've try something like this too,

SELECT Report_filter_temp.[Beam] AS Beam, 'All' AS Location, 'All' AS Channel, Avg([Report_filter_temp].[Pk-Pk_deviation(dB)]) AS [Mean of Pk-Pk Deviation], StDev([Report_filter_temp].[Pk-Pk_deviation(dB)]) AS [Standard Deviation of Pk-Pk Deviation], Count(Report_filter_temp.[Product_ID]) AS [Number of acquisitions]
FROM Report_filter_temp LEFT JOIN  Report_filter_temp_Count_Distinct ON Report_filter_temp.[Number of acquisitions] > Report_filter_temp_Count_Distinct.[CountOfProduct_ID]
GROUP BY Report_filter_temp.[Beam];

But i'm getting an error that the Report_filter_temp.[Number of acquisitions] could refer to more than one table in the FROM clause (event if the statement IS in the from clause ....)

[Gabriedl23]

If something like that could work,it would be perfect

SELECT DT_Analysis_Results_Table.[Beam] AS Beam, 'All' AS Location, 'All' AS Channel, Avg([DT_Analysis_Results_Table].[Pk-Pk_deviation(dB)]) AS [Mean of Pk-Pk Deviation], StDev([DT_Analysis_Results_Table].[Pk-Pk_deviation(dB)]) AS [Standard Deviation of Pk-Pk Deviation],Count(SELECT DISTINCT DT_Analysis_Results_Table.[Product_ID] AS [A] FROM Report_filter_temp GROUP BY DT_Analysis_Results_Table.[Beam];
) AS [Number of acquisitions]
FROM Report_filter_temp
GROUP BY DT_Analysis_Results_Table.[Beam];

[Gabriedl23]

Here where i am

Select Report_filter_temp.[Beam] AS Beam, 'All' AS Location, 'All' AS Channel, Avg([Report_filter_temp].[Pk-Pk_deviation(dB)]) AS [Mean of Pk-Pk Deviation], StDev([Report_filter_temp].[Pk-Pk_deviation(dB)]) AS [Standard Deviation of Pk-Pk Deviation], T2.N AS [NbOfAcquisition], Count(Report_filter_temp.[Product_ID]) AS [woot], Report_filter_temp.[Product_ID]
FROM
          (SELECT T.Product_ID, Count (T.Product_ID) AS N
          FROM
                   (SELECT DISTINCT Product_ID, Count(*) AS N
                    FROM Report_filter_temp GROUP BY Product_ID) AS T
         GROUP BY T.Product_ID) AS T2
INNER JOIN  Report_filter_temp AS Report_filter_temp 
ON Report_filter_temp.[Product_ID] = T2.Product_ID 
GROUP BY Report_filter_temp.[Beam];

[Gabriedl23]

FINALLY I GOT IT !!!!!! 24 hours of work to find that

SELECT Report_filter_temp.Beam AS Beam, 'All' AS Location, 'All' AS Channel, Avg(Report_filter_temp.[Pk-Pk_deviation(dB)]) AS [Mean of Pk-Pk Deviation], StDev(Report_filter_temp.[Pk-Pk_deviation(dB)]) AS [Standard Deviation of Pk-Pk Deviation], T2.N AS NbOfAcquisition, Count(Report_filter_temp.Product_ID) AS woot
FROM (SELECT Count(T.A) AS N, T.Beam AS Beam
      FROM
                    (SELECT DISTINCT Report_filter_temp.Product_ID AS A, Report_filter_temp.Beam
                    FROM Report_filter_temp)  AS T
      GROUP BY T.BEAM)  AS T2 
INNER JOIN Report_filter_temp ON T2.Beam =Report_filter_temp.Beam
GROUP BY Report_filter_temp.Beam, T2.N;

[Gabriedl23]

Thx for all the help guys