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]
精彩评论