开发者

select Distinct TSQL with totals of distinct

I am looking to make a select statment that selects all the distinct items in a row, and provides the totals of each result

SELECT DISTINCT [Column 16] FROM [tab]

and thats h开发者_如何学Cow far my TSQL goes


Are you looking for

SELECT [Column 16], COUNT(*) 
FROM [tab]
GROUP BY [Column 16]
--WITH ROLLUP (if you need to the total )
ORDER BY COUNT(*) 

You can append DESC keyword to the end of the query, i.e. ORDER BY COUNT(*) DESC if you want your resultset to be ordered in descending order, or ASC for ascending (ascending is default, you can omit it).


When you GROUP BY, you get a distinct list of [Column 16]. Then you can use an aggregate function with it.

SELECT [Column 16], Sum ( [Column To Sum] ) Total_T1
FROM [tab]
Group By [Column 16]

If you want to group by multiple columns they should appear in the select and in the GROUP BY

SELECT [Column 16], [Column 17], Sum ( [Column To Sum] ) Total_T1
FROM [tab]
Group By [Column 16], [Column 17]

You can also specify multiple aggregations (these do not appear in the group by clause

SELECT [Column 16], [Column 17], 
      Sum ( [Column To Sum] ) Total_T1,
      Count ( [Column To Sum] ) NumOf_T1,
      Avg ( [Column To Sum] ) Avg_T1,
      Min ( [Column To Sum] ) Min_T1,
      Max ( [Column To Sum] ) Max_T1
FROM [tab]
Group By [Column 16], [Column 17]
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜