在SQL Server中使用GROUP BY

With this query I got 2 columns of distinct values

通过这个查询，我得到了两列不同的值。

SELECT DISTINCT 
    Transport.ID, Transport.Address 
FROM  
    Cars 
LEFT JOIN 
    Transport ON Cars.TransportFrom=Transport.ID 
ORDER BY 
    address

Now I need get one more column with sum:

现在我需要再加一列和:

SUM(CASE WHEN Active='True' THEN 1 ELSE 0 END) 

But I get error if I do queries like

但如果我做这样的查询，就会出错

SELECT DISTINCT 
    Transport.ID, Transport.Address, 
    SUM(CASE WHEN Active = 'True' THEN 1 ELSE 0 END)
FROM 
    Cars 
LEFT JOIN 
    Transport ON Cars.TransportFrom = Transport.ID 
ORDER BY 
    address

Msg 8120, Level 16, State 1, Line 1
Column 'Transport.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Msg 8120, 16级，状态1，第一列运输。ID'在select列表中无效，因为它不包含在聚合函数或GROUP BY子句中。

SELECT 
    Transport.ID, Transport.Address, 
    SUM(CASE WHEN Active = 'True' THEN 1 ELSE 0 END)
FROM 
    Cars 
LEFT JOIN 
    Transport ON Cars.TransportFrom = Transport.ID 
ORDER BY 
    address
GROUP BY 
    ID

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'GROUP'.

第156，第15级，状态1，第5行不正确的语法接近关键字'组'。

SELECT 
    Transport.ID, Transport.Address, 
    SUM(CASE WHEN Active = 'True' THEN 1 ELSE 0 END)
FROM 
    Cars 
LEFT JOIN 
    Transport ON Cars.TransportFrom = Transport.ID 
GROUP BY 
    ID
ORDER BY 
    address

Msg 209, Level 16, State 1, Line 4
Ambiguous column name 'ID'.

Msg 209, 16级，状态1，第4行不明确的列名“ID”。

SELECT 
    Transport.ID, Transport.Address, 
    SUM(CASE WHEN Active = 'True' THEN 1 ELSE 0 END)
FROM 
    Cars 
LEFT JOIN 
    Transport ON Cars.TransportFrom = Transport.ID 
GROUP BY 
    Transport.ID
ORDER BY 
    address

Msg 8120, Level 16, State 1, Line 1
Column 'Transport.address' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Msg 8120, 16级，状态1，第一列运输。地址“在选择列表中无效，因为它不包含在聚合函数或GROUP BY子句中。

How to get the syntax right?

如何使语法正确?

1 个解决方案

#1

Well the error messages are clear, no ?

错误信息很清楚，不是吗?

SELECT Transport.ID, Transport.Address, SUM(CASE WHEN Active='True' THEN 1 ELSE 0 END)
FROM Cars 
LEFT JOIN Transport ON Cars.TransportFrom=Transport.ID 
--add all the fields from select clause which are not in an aggregation function
GROUP BY Transport.ID, Transport.Address 
ORDER BY address