getting all the distinct columns columns count
-
hi
is there a way to get all values and there count of distinct
for example what we are doing is
SELECT [ProductID], COUNT(ProductID)AS Expr1 FROM [Order Details] GROUP BY [ProductID]
the above will give all the productId and count of there distinct value but only for the productId.can we the above for all the columns of a table at once??
or that we provide the columnname and it gives the values??
thnx
Amrita
-
SELECT [ProductID], COUNT(*) AS Expr1 FROM [Order Details] GROUP BY [ProductID]
Regards,
gova -
For a simple count of all distinct values you can do this :
Select count(Distinct name) as name, count(Distinct id) as id from dbo.ObjSQL
-
hi
thnx for the reply but this is exactly wat i have written.
basically wat we need is if there are other columns like orderId,CustomerId ....
it gives distinct of all these columns
something like
SELECT [ProductID], COUNT([ProductID]) AS Expr1 FROM [Order Details] GROUP BY [ProductID]
;
SELECT [OrderId], COUNT([OrderId]) AS Expr1 FROM [Order Details] GROUP BY [OrderId]
but the above result will give different,can it be converted into columns
Amrita
-
It can't be done the way you want, the number of rows return will change with each column. You'll have to do 1 query per column (if you need the column value associated with the count too).
-
Some one did not get the question. It was me. May be again.
Null values are ignored in aggregate function if you want to ignore them try using this
SELECT COUNT(DISTINCT COALESCE(ProductID, '')) ProdCount, COUNT(DISTINCT COALESCE([OrderID], '')) OrderCount
FROM
[Order Details]
Regards,
gova -
hi
thnx for all the help
SELECT COUNT(DISTINCT COALESCE(ProductID, '')) ProdCount, COUNT(DISTINCT COALESCE([OrderID], '')) OrderCount
FROM
[Order Details]
will only give the total distincrt value. wat we need is the actual ProductID , its distinct count ,actual OrderID, its distinct count etc for each column...
we are implementing the above in vb.net so if there is any other way to implement in vb.net it wud be grt
amrita
-
Didn't you read this???
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply