Post reply

getting all the distinct columns columns count

  • June 27, 2005 at 5:51 am

    #67203

    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

  • June 27, 2005 at 6:18 am

    #568541

    SELECT [ProductID], COUNT(*) AS Expr1 FROM [Order Details] GROUP BY [ProductID]

    Regards,
    gova

  • June 27, 2005 at 6:43 am

    #568551

    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

  • June 27, 2005 at 6:43 am

    #568550

    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

     

  • June 27, 2005 at 6:48 am

    #568554

    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).

  • June 27, 2005 at 6:52 am

    #568555

    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

  • June 27, 2005 at 7:30 am

    #568568

    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

     

  • June 27, 2005 at 7:41 am

    #568572

    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