Geek asks and answers

Why is this simple SQL query not working?

So, I am working on SQL Server 2008, and I have this query, which should be fairly simple, but for some reason does not work. It basically looks like this:

SELECT TOP 10
    u.Id                AS "UserId",
    u.CreationDate      AS "Member since",
    AVG(q.Score)        AS "Average Question Rating",  
    COUNT(q.Id)         AS "N. of Questions posted by the agent",
    AVG(a.Score)        AS "Average Answer Rating",  
    COUNT(a.Id)         AS "N. of Answers posted by the agent"
FROM    
        Users u, 
        Answers a, 
        Questions q
WHERE q.OwnerUserId = u.Id
AND a.OwnerUserId = u.Id
GROUP BY u.Id, u.CreationDate

When I work only in the table of answers or in the table "Questions", everything is in order. But as soon as I try to do both at once (for example, in the request above), COUNT do not work at all. I get that COUNT (a.Id) is identical to COUNT (q.Id). Therefore, I tried to reduce my query in order to understand what was wrong, and I realized that I just needed to add the table “Questions or Answers” ​​(without even using them anywhere) to the FROM clause when working with another table, and that’s all was destroyed.

, , , , , - , . .

+3
4

Answers Questions . Answers Questions ( )

- :

SELECT TOP 10
    u.Id                AS "UserId",
    u.CreationDate      AS "Member since",
    ISNULL((SELECT AVG(Score) FROM Answers   WHERE OwnerUserId = u.Id), 0)
                        AS "Average Question Rating",  
           (SELECT COUNT(*)   FROM Answers   WHERE OwnerUserId = u.Id)        
                        AS "N. of Questions posted by the agent",
    ISNULL((SELECT AVG(Score) FROM Questions WHERE OwnerUserId = u.Id), 0)
                        AS "Average Answer Rating",  
           (SELECT COUNT(*)   FROM Questions WHERE OwnerUserId = u.Id)
                        AS "N. of Answers posted by the agent"
FROM  Users u

, :

SELECT TOP 10
     u.Id                AS "UserId",
     u.CreationDate      AS "Member since",
     ISNULL(q.a, 0)      AS "Average Question Rating",  
     ISNULL(q.c, 0)      AS "N. of Questions posted by the agent",
     ISNULL(a.a, 0)      AS "Average Answer Rating",  
     ISNULL(a.c, 0)      AS "N. of Answers posted by the agent"
FROM Users u
-- If you LEFT JOIN these tables, you'll get also results for users without
-- questions or answers
LEFT OUTER JOIN (SELECT OwnerUserId, AVG(Score) a, COUNT(*) c 
     FROM Questions GROUP BY OwnerUserId) q
     ON  q.OwnerUserId = u.Id
LEFT OUTER JOIN (SELECT OwnerUserId, AVG(Score) a, COUNT(*) c 
     FROM Answers GROUP BY OwnerUserId) a
     ON  a.OwnerUserId = u.Id

SQL Server , , . , SQL Server. .

+5

, , , . :

SELECT TOP 10
    u.Id                AS "UserId",
    u.CreationDate      AS "Member since",
    AVG(q_score)        AS "Average Question Rating",  
    COUNT(q_id)         AS "N. of Questions posted by the agent",
    AVG(a_score)        AS "Average Answer Rating",  
    COUNT(a_id)         AS "N. of Answers posted by the agent"
FROM Users u
JOIN (select OwnerUserId,
             Score        q_score,
             Id           q_id,
             NULL         a_score,
             NULL         a_id
      from Answers
      union all
      select OwnerUserId,
             NULL         q_score,
             NULL         q_id,
             Score        a_score,
             Id           a_id
      from Questions) qa
  ON qa.OwnerUserId = u.Id
GROUP BY u.Id, u.CreationDate
+3

, DISTINCT ?

SELECT TOP 10 
    u.Id                         AS "UserId", 
    u.CreationDate               AS "Member since", 
    AVG(q.Score)                 AS "Average Question Rating",   
    COUNT(DISTINCT q.Id)         AS "N. of Questions posted by the agent", 
    AVG(a.Score)                 AS "Average Answer Rating",   
    COUNT(DISTINCT a.Id)          AS "N. of Answers posted by the agent" 
FROM     
        Users u,  
        Answers a,  
        Questions q 
WHERE q.OwnerUserId = u.Id 
AND a.OwnerUserId = u.Id 
GROUP BY u.Id, u.CreationDate
+1

, ( ). , ?

SELECT TOP 10
    u.Id                AS "UserId",
    u.CreationDate      AS "Member since",
    AVG(q.Score)        AS "Average Question Rating",  
    COUNT(q.Id)         AS "N. of Questions posted by the agent",
    AVG(a.Score)        AS "Average Answer Rating",  
    COUNT(a.Id)         AS "N. of Answers posted by the agent"
FROM    
        Users u, 
        Answers a, 
        Questions q
WHERE q.OwnerUserId = u.Id
AND a.OwnerUserId = u.Id
GROUP BY u.Id, u.CreationDate

would be

SELECT TOP 10
    u.Id                AS "UserId",
    u.CreationDate      AS "Member since",
    AVG(q.Score)        AS "Average Question Rating",  
    COUNT(q.Id)         AS "N. of Questions posted by the agent",
    AVG(a.Score)        AS "Average Answer Rating",  
    COUNT(a.Id)         AS "N. of Answers posted by the agent"
FROM    
        Users u
JOIN Answers a on u.ID = a.ID (assuming thats how answers and users are linked).
JOIN Questions q on a.ID = q.ID (assuming thats how questions and answers are linked)
WHERE q.OwnerUserId = u.Id
AND a.OwnerUserId = u.Id
GROUP BY u.Id, u.CreationDate
-1

More articles:


All Articles