July 18, 2006 at 1:09 pm
I have this query:
SELECT distinct [MVG T003 - Eliminate outliers from T001].StdVin,
([MVG T003 - Eliminate outliers from T001].MODELYEAR) AS FirstOfMODELYEAR,
([MVG T003 - Eliminate outliers from T001].MAKE) AS FirstOfMAKE,
([MVG T003 - Eliminate outliers from T001].SERIES) AS FirstOfSERIES,
([MVG T003 - Eliminate outliers from T001].BODYSTYLE) AS FirstOfBODYSTYLE,
Round(StDev([MVG T003 - Eliminate outliers from T001].[CurValue]),0) AS STDEVAUCNETPRICE,
Count([MVG T003 - Eliminate outliers from T001].StdVin) AS CountOfStdVin,
Avg([MVG T003 - Eliminate outliers from T001].CurValue) AS AvgOfCurValue,
([MVG T003 - Eliminate outliers from T001].VIC) AS VIC
into NelsonTest
FROM [MVG T003 - Eliminate outliers from T001] where stdvin='1C3EL45R050000000'
GROUP BY [MVG T003 - Eliminate outliers from T001].StdVin,
[MVG T003 - Eliminate outliers from T001].MODELYEAR,
[MVG T003 - Eliminate outliers from T001].MAKE,
[MVG T003 - Eliminate outliers from T001].SERIES,
[MVG T003 - Eliminate outliers from T001].BODYSTYLE,
[MVG T003 - Eliminate outliers from T001].VIC
my problem is=> I need the results in group by StdVin only, how can I do that?
thanks in advance
July 18, 2006 at 2:50 pm
You could try the following:
SELECT [MVG T003 - Eliminate outliers from T001].StdVin,
GRP.FirstOfMODELYEAR,
GRP.FirstOfMAKE,
GRP.FirstOfSERIES,
GRP.FirstOfBODYSTYLE,
GRP.STDEVAUCNETPRICE,
GRP.CountOfStdVin,
GRP.AvgOfCurValue,
GRP.VIC
INTO NelsonTest
FROM [MVG T003 - Eliminate outliers from T001]
INNER JOIN( SELECT [MVG T003 - Eliminate outliers from T001].StdVin,
([MVG T003 - Eliminate outliers from T001].MODELYEAR) AS FirstOfMODELYEAR,
([MVG T003 - Eliminate outliers from T001].MAKE) AS FirstOfMAKE,
([MVG T003 - Eliminate outliers from T001].SERIES) AS FirstOfSERIES,
([MVG T003 - Eliminate outliers from T001].BODYSTYLE) AS FirstOfBODYSTYLE,
ROUND( STDEV( [MVG T003 - Eliminate outliers from T001].[CurValue]), 0) AS STDEVAUCNETPRICE,
COUNT( [MVG T003 - Eliminate outliers from T001].StdVin) AS CountOfStdVin,
AVG( [MVG T003 - Eliminate outliers from T001].CurValue) AS AvgOfCurValue,
([MVG T003 - Eliminate outliers from T001].VIC) AS VIC
FROM [MVG T003 - Eliminate outliers from T001]
WHERE [MVG T003 - Eliminate outliers from T001].stdvin = '1C3EL45R050000000'
GROUP BY [MVG T003 - Eliminate outliers from T001].StdVin,
[MVG T003 - Eliminate outliers from T001].MODELYEAR,
[MVG T003 - Eliminate outliers from T001].MAKE,
[MVG T003 - Eliminate outliers from T001].SERIES,
[MVG T003 - Eliminate outliers from T001].BODYSTYLE,
[MVG T003 - Eliminate outliers from T001].VIC) GRP ON( [MVG T003 - Eliminate outliers from T001].StdVin = GRP.StdVin)
GROUP BY [MVG T003 - Eliminate outliers from T001].StdVin
I wasn't born stupid - I had to study.
July 19, 2006 at 10:07 am
I'm not 100% sure what you mean here - but I'm assuming the modelyear, make, series, VIC and bodystyle should be unique for any given VIN, should it? So grouping by them would be unnecessary, in fact, and you're only doing it in your query because you want them all to come out in your row of results?
In which case you could just use the MIN() function to get the first (which in fact is the ONLY) value for each of those columns above, i.e.
SELECT distinct [MVG T003 - Eliminate outliers from T001].StdVin,
MIN([MVG T003 - Eliminate outliers from T001].MODELYEAR) AS FirstOfMODELYEAR,
MIN([MVG T003 - Eliminate outliers from T001].MAKE) AS FirstOfMAKE,
MIN([MVG T003 - Eliminate outliers from T001].SERIES) AS FirstOfSERIES,
MIN([MVG T003 - Eliminate outliers from T001].BODYSTYLE) AS FirstOfBODYSTYLE,
Round(StDev([MVG T003 - Eliminate outliers from T001].[CurValue]),0) AS STDEVAUCNETPRICE,
Count([MVG T003 - Eliminate outliers from T001].StdVin) AS CountOfStdVin,
Avg([MVG T003 - Eliminate outliers from T001].CurValue) AS AvgOfCurValue,
MIN([MVG T003 - Eliminate outliers from T001].VIC) AS VIC
into NelsonTest
FROM [MVG T003 - Eliminate outliers from T001] where stdvin='1C3EL45R050000000'
GROUP BY [MVG T003 - Eliminate outliers from T001].StdVin,
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply