Solved

CASE WHEN COUNT and GROUP BY

Posted on 2004-10-29
225 Views
Last Modified: 2012-05-05
Greetings...I have the following statement that produces the expected results of id, Name and Points. However, I would like to 'sum' the count where names are duplicates. In other words, if a name has two entries (each having Points=1) I would like one entry where Points=2.

If I uncomment my GROUP BY I get only one name, but the Points value remains 1. What am I doing wrong? Thanks...Scott

SELECT b.[id],
            b.[Name],
            CASE WHEN ((TrackingDateTime) IS NOT NULL AND (ReceivedByHESDateTime) IS NOT NULL) THEN 1 ELSE 0 END AS Points
      FROM DocumentTracking a
      INNER JOIN companies b ON (a.companyid = b.[id])
      WHERE TrackingDateTime >= '10/28/03'
            AND TrackingDateTime <= '10/28/04'
      --GROUP BY b.[id], b.[name]
      ORDER BY b.[name]
0
Question by:rsanglim
    6 Comments
     
    LVL 26

    Accepted Solution

    by:
    try

    SELECT b.[id],
              b.[Name],
              SUM(CASE WHEN ((TrackingDateTime) IS NOT NULL AND (ReceivedByHESDateTime) IS NOT NULL) THEN 1 ELSE 0 END) AS Points
         FROM DocumentTracking a
         INNER JOIN companies b ON (a.companyid = b.[id])
         WHERE TrackingDateTime >= '10/28/03'
              AND TrackingDateTime <= '10/28/04'
         GROUP BY b.[id], b.[name]
         ORDER BY b.[name]
    0
     
    LVL 26

    Expert Comment

    by:Hilaire
    I just added a SUM group function to collapse the records that belong to the same group into one single record
    0
     
    LVL 48

    Expert Comment

    by:Julian Hansen
    I think Hilaire has nailed it.

    The GROUP BY works with the agregate functions SUM, MAX, MIN etc without specifying an agregate function SQL does not know what you want to do - i.e. it cannot know that you want to sum the points - you might want to find the MAX or the MIN.


    0
     

    Author Comment

    by:rsanglim
    Thanks, Hilaire...that was it! I appreciate the quick reply :)
    0
     

    Author Comment

    by:rsanglim
    ...and thanks julian for the explanation!
    0
     
    LVL 48

    Expert Comment

    by:Julian Hansen
    ;) you are most welcome
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Suggested Solutions

    Title # Comments Views Activity
    Data lost in SQL server 23 60
    SQL Select * from 6 16
    replace quotes with UTF-8 character 38 42
    t-sql help 11 28
    By Mark Wills We often hear about Fragmentation, and generally have an idea that it is about broken bits, or bad for performance, or at least, is generally not a good thing. But what does it really mean ? By way of analogy, think of the humbl…
    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    877 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now