quote:

---

Originally posted by Johnyalm

I would like to do two queries in one, please help me out. The following query creates a list of dimensions in a test with Average and Std-deviation for each dimension. And I get the result ONLY for Women (tblACTORS.gender = 'W'). Now I would like to get the same for MEN (tblACTORS.gender = 'M') at the sam time and maybe as well get differenses in qverage and stddev in ONE query.

Is there a way to reconstruct the query?


SELECT CAST(AVG(tblDIMRESULTS.scaledscore) AS integer) AS average,
CAST(STDEV(tblDIMRESULTS.scaledscore) AS decimal(10, 2)) AS stddev,
tblDIMENSIONS.dimension,
tblDIMRESULTSINFO.displayorder
FROM tblTESTS
INNER JOIN
tblDIMRESULTS ON tblTESTS.id_tests = tblDIMRESULTS.id_tests
INNER JOIN
tblDIMRESULTSINFO ON tblDIMRESULTS.id_dimresultsinfo = tblDIMRESULTSINFO.id_dimresultsinfo
INNER JOIN
tblDIMENSIONS ON tblDIMRESULTSINFO.id_dimensions = tblDIMENSIONS.id_dimensions
INNER JOIN
tblACTORS ON tblTESTS.id_actors = tblACTORS.id_actors
INNER JOIN
tblFORMDATA ON tblACTORS.id_actors = tblFORMDATA.id_actors
WHERE
(tblTESTS.testname = 'mbp')
AND
(tblACTORS.gender = 'W')
GROUP BY tblDIMENSIONS.dimension, tblDIMRESULTSINFO.displayorder
ORDER BY tblDIMRESULTSINFO.displayorder


----
Johny Alm

www.mirrorgate.com

---

SELECT * FROM (
    SELECT 'Women' AS Gender
	 , tblDIMENSIONS.dimension,
	 , tblDIMRESULTSINFO.displayorder
	 , CAST(AVG(tblDIMRESULTS.scaledscore) AS integer) AS average 
	 , CAST(STDEV(tblDIMRESULTS.scaledscore) AS decimal(10, 2)) AS stddev,
      FROM tblTESTS t
INNER JOIN tblDIMRESULTS r
	ON tblTESTS.id_tests = tblDIMRESULTS.id_tests 
INNER JOIN tblDIMRESULTSINFO i 
	ON tblDIMRESULTS.id_dimresultsinfo = tblDIMRESULTSINFO.id_dimresultsinfo 
INNER JOIN tblDIMENSIONS d
	ON tblDIMRESULTSINFO.id_dimensions = tblDIMENSIONS.id_dimensions 
INNER JOIN tblACTORS a 
	ON tblTESTS.id_actors = tblACTORS.id_actors 
INNER JOIN tblFORMDATA f 
	ON tblACTORS.id_actors = tblFORMDATA.id_actors
     WHERE (tblTESTS.testname = 'mbp') 
       AND  (tblACTORS.gender = 'W')
  GROUP BY tblDIMENSIONS.dimension, tblDIMRESULTSINFO.displayorder
  ORDER BY tblDIMRESULTSINFO.displayorder
) AS XXX

UNION ALL

SELECT * FROM (
    SELECT 'Men' AS Gender
	 , tblDIMENSIONS.dimension,
	 , tblDIMRESULTSINFO.displayorder
	 , CAST(AVG(tblDIMRESULTS.scaledscore) AS integer) AS average 
	 , CAST(STDEV(tblDIMRESULTS.scaledscore) AS decimal(10, 2)) AS stddev,
      FROM tblTESTS t
INNER JOIN tblDIMRESULTS r
	ON tblTESTS.id_tests = tblDIMRESULTS.id_tests 
INNER JOIN tblDIMRESULTSINFO i 
	ON tblDIMRESULTS.id_dimresultsinfo = tblDIMRESULTSINFO.id_dimresultsinfo 
INNER JOIN tblDIMENSIONS d
	ON tblDIMRESULTSINFO.id_dimensions = tblDIMENSIONS.id_dimensions 
INNER JOIN tblACTORS a 
	ON tblTESTS.id_actors = tblACTORS.id_actors 
INNER JOIN tblFORMDATA f 
	ON tblACTORS.id_actors = tblFORMDATA.id_actors
     WHERE (tblTESTS.testname = 'mbp') 
       AND  (tblACTORS.gender = 'M')
  GROUP BY tblDIMENSIONS.dimension, tblDIMRESULTSINFO.displayorder
  ORDER BY tblDIMRESULTSINFO.displayorder
) AS YYY

quote:

---

Originally posted by TG

Well, I didn't study your query but I won't let that stop me from throwing out a suggestion:
could you remove the "where gender.." and add gender to the group by?
or
union the 2 statements (gender='m' and gender='w')

Be One with the Optimizer
TG

---

quote:

---

Originally posted by TG

Well, I didn't study your query but I won't let that stop me from throwing out a suggestion:
could you remove the "where gender.." and add gender to the group by?
or
union the 2 statements (gender='m' and gender='w')

Be One with the Optimizer
TG

---

quote:

---

Originally posted by X002548

Are you telling me you didn't cut and paste my code sample and test it?



Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

---