[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

CASE WHEN COUNT and GROUP BY

Posted on 2004-10-29
6
Medium Priority
?
233 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
Comment
Question by:rsanglim
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 26

Accepted Solution

by:
Hilaire earned 500 total points
ID: 12445174
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
ID: 12445192
I just added a SUM group function to collapse the records that belong to the same group into one single record
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 12445280
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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 

Author Comment

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

Author Comment

by:rsanglim
ID: 12445474
...and thanks julian for the explanation!
0
 
LVL 59

Expert Comment

by:Julian Hansen
ID: 12445662
;) you are most welcome
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

656 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