complicated query help. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

complicated query help.

Author

Topic

monchichi
Starting Member

1 Post

Posted - 2008-04-01 : 21:42:24

hi,please help i want to get the sum of Cp=((partlist.usl-partlist.lsl)/(6*stdev(spcdata.data))


SELECT SpcData.PartNo, SpcData.Dimension, SpcData.MachineNo, 
      SUM(SpcData.SampleNo / 5) AS subgroup, STDEV(SpcData.Data) AS stdevdata, 
      PartList.USL, PartList.LSL, SUM((PartList.USL - PartList.LSL) / (6 * STDEV(SpcData.Data) AS stdevdata))) AS cp
FROM SpcData INNER JOIN
      PartList ON SpcData.PartNo = PartList.PartNo AND 
      SpcData.Dimension = PartList.Dimension
GROUP BY SpcData.PartNo, SpcData.Dimension, SpcData.MachineNo, PartList.USL, 
      PartList.LSL

please help how to get the correct query result.thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-02 : 12:37:07

[code]SELECT SpcData.PartNo, SpcData.Dimension, SpcData.MachineNo,
SUM(SpcData.SampleNo / 5) AS subgroup, STDEV(SpcData.Data) AS stdevdata,
PartList.USL, PartList.LSL,
FROM SpcData INNER JOIN
PartList ON SpcData.PartNo = PartList.PartNo AND
SpcData.Dimension = PartList.Dimension
INNER JOIN (SELECT PartNo,SUM((PartList.USL - PartList.LSL) / (6 * STDEV(SpcData.Data) AS stdevdata))) AS cp
FROM PartList
GROUP BY PartNo
)t
ON t.cp=PartList=PartNo
GROUP BY SpcData.PartNo, SpcData.Dimension, SpcData.MachineNo, PartList.USL,
PartList.LSL[/code]

And if it does not provide you the result, post your table DDL & also sample o/p

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-02 : 13:00:07

I don't think you can have two aggregate functions on top of each other.
You are summing a STDEV aggregation.

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources