CODEWITHSUNDEEP
sqlselectconditional-statements
user1072918
user1072918

Reputation:

SQL query to select rows based on condition

SELECT
    MNo, X, Y, Z
FROM
    Coord C
    JOIN Result R ON R.ResultID = C.ResultID
    JOIN Member M ON M.MemberID = R.MemberID
WHERE
    M.StdID = @stdID
GROUP BY
    MNo

I have a query witch gives me x-, y- and z-coordinates for every Member represented by MNo. Some Members can have more than one row of coordinates, and I would like to get the mean of those coordinates (and only those), like

MNo     x       y       z
15      10.6    12.3    20.1
16      11.1    17.8    14.0

omitting Members 1-14 and 17- with only one row of coordinates. How can I do this?

Upvotes: 1

Views: 4655

Answers (1)

troy
troy

Reputation: 422

For part 1 of your question - averaging, AVG is what you need.

For part 2 of your question - how to filter out members 1-14 and 17-, you want a WHERE clause.

For part 3 of your question - how to get rid of members with only 1 row of coordinates, you want a HAVING clause.

Bringing those together, we have:

SELECT
    MNo, AVG(X) AS X, AVG(Y) AS Y, AVG(Z) AS Z
FROM
    Coord C
    JOIN Result R ON R.ResultID = C.ResultID
    JOIN Member M ON M.MemberID = R.MemberID
WHERE
    M.StdID = @stdID
   AND M.MNo NOT BETWEEN 1 AND 14
   AND M.MNo NOT BETWEEN 17 and 20
GROUP BY
    MNo
HAVING
    COUNT(1) > 1

Notes:

  • If you only called out members 1-14 and 17- because those are the members with only one set of coordinates, then you can ignore the two "AND M.MNo NOT IN" statements.
  • The field in the COUNT(1) doesn't really matter (could alternatively be COUNT(*) or COUNT(X), etc. - any field will return the number of rows, and the HAVING filter will remove cases where there was only 1 row.

Upvotes: 2

Related Questions