From: hugo@perFact.REMOVETHIS.info.INVALID   
      
   Op 28-10-2010 21:28, Luciano (DOC) schreef:   
   > I have a table having this structure:   
   > idrecord   
   > nation   
   > value   
   > What I have to get by a query is grouping records by nation, summing and   
   > counting the values, dividing negative by positive.   
   > Therefore:   
   > USA, sumOfPositiveValues, CountOfPositiveValues, sumOfNegativeValues,   
   > CountOfNegativeValues   
   > ITALY, sumOfPositiveValues, CountOfPositiveValues, sumOfNegativeValues,   
   > CountOfNegativeValues   
   > FRANCE, sumOfPositiveValues, CountOfPositiveValues, sumOfNegativeValues,   
   > CountOfNegativeValues   
   > I hope I was clear enough :-)   
   > Thanks.   
   >   
   > Luciano   
      
   Hi Luciano,   
      
   You can use something like this:   
      
   SELECT   Nation,   
             SUM(CASE WHEN Value >= 0 THEN Value END),   
             COUNT(CASE WHEN Value >= 0 THEN Value END),   
             SUM(CASE WHEN Value < 0 THEN Value END),   
             COUNT(CASE WHEN Value < 0 THEN Value END)0   
   FROM     YourTable   
   GROUP BY Nation;   
      
   (untested - for a tested reply, please see www.aspfaq.com/5006)   
      
      
   --   
   Hugo Kornelis, SQL Server MVP   
   My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)