Post reply

Group by

  • June 7, 2005 at 11:34 am

    #87907

    Sample Data:

    WhseBatchData

    11fdsafjkdf

    11regherjy

    12eddfjf

    12bvgrbgrbd

    12tyhnytrjt

    13sdfvksdfgvj;

    15sfdlkklfsdgv

    23fsdlkvjgfsdlk;g

    24fdsl;kjgvgfsdkg

    37fj;egjklfg

    37vfbvggrfbv

    37yrjhyt

    37rtyjhyrtr

    37rtjhtryuj

    I want to group by Whse, yet I want the count of the number batches for each of the warehouses, thus the results would be:

    WhseNoOfBatches

    14

    22

    31

    Thanks,

  • June 7, 2005 at 11:40 am

    #563621

    Select whse, batch, count(*) as Total

    from dbo.YourTable

    group by Whse, Batch

    [Edit]

    Sorry didn't understand the post correctly... gonna repost the right answer.

  • June 7, 2005 at 11:41 am

    #563622

    Have you tried

    SELECT t1.Whse, COUNT(*) FROM

      (SELECT DISTINCT Whse, Batch FROM NameOfTableHere) t1 GROUP BY Whse  ????



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • June 7, 2005 at 11:42 am

    #563623

    Or

    Select Whse, count(Distinct Batch) from dbo.YourTable group by Whse

  • June 7, 2005 at 11:48 am

    #563624

    AJ Ahrens

    Remi Gregoire

    Your last queries worked great!

    thanks,

    Dean

  • June 7, 2005 at 12:16 pm

    #563631

    Just rechecked to see which one was performing better, but they use the same execution plan so you may chose the one that you like best just by its looks (if only life could be like that ).

  • June 8, 2005 at 12:48 pm

    #563938

    Personally (as usual) I prefer Remi's.  I like the look and it is LESS typing



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • June 8, 2005 at 12:52 pm

    #563939

    Always a good quality in a query .

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply