Sql-server – Case Statement With Group By

pivotsql serversql-server-2008-r2

I think it is my group by in this syntax that is skewing my desired result set. I ONLY want to see the data I am requesting, however my query returns all the data like this:

salesman    Q1 2015 Q1 2016
Blue    NULL    NULL 
Blue    NULL    NULL
Blue    200     NULL
Blue    300     NULL
Blue    NULL    400
Blue    NULL    600

What I (thought) my syntax would return would ONLY be the below

salesman    [Q1 2015]   [Q1 2016]
Blue          500          1000

This is my DDL (well garbage data, but gets point across)

Create Table Barnacles
(
  warrantypurchasedate date
  ,salesman varchar(500)
  ,itemprice float
)
Insert Into Barnacles (warrantypurchasedate, salesman, itemprice) Values
('01/01/2014', 'Blue', 100)
,('02/01/2014', 'Blue', 300)
,('01/01/2015', 'Blue', 200)
,('02/01/2015', 'Blue', 300)
,('01/01/2016', 'Blue', 400)
,('02/01/2016', 'Blue', 600)

This is the query syntax I attempted:

Select
salesman
,case when DATEPART(Year, warrantypurchasedate) = '2015' AND DATEPART(Quarter, warrantypurchasedate) = '1' then SUM(itemprice) else NULL end As [Q1 2015]
,case when DATEPART(Year, warrantypurchasedate) = '2016' AND DATEPART(Quarter, warrantypurchasedate) = '1' then SUM(itemprice) else NULL end As [Q1 2016]
FROM Barnacles
GROUP BY salesman, warrantypurchasedate

What should I alter in my query so that I only see the SUM() for Q1 15 and the SUM() for Q1 16 not a row returned for each entry in the table?

Best Answer

Untested, but I believe your query should look like:

SELECT salesman
      ,sum(case when DATEPART(Year, warrantypurchasedate) = '2015'
                 and DATEPART(Quarter, warrantypurchasedate) = '1'
                then itemprice 
           end) As [Q1 2015]
      ,sum(case when DATEPART(Year, warrantypurchasedate) = '2016'
                 and DATEPART(Quarter, warrantypurchasedate) = '1'
                then itemprice 
           end) As [Q1 2016]
FROM Barnacles
WHERE DATEPART(Year, warrantypurchasedate) in ('2015', '2016')
  AND DATEPART(Quarter, warrantypurchasedate) = '1' 
GROUP BY salesman

I moved sum outside of case, and added a WHERE clause to filter out relevent rows. I also removed date from the GROUP BY clause, otherwise you will get 1 row per date.

Related Solutions

Sql-server – Check Constraint with Case Statement

Check constraints can only be used to verify if the data falls within a given set of parameters. The CHECK(...) clause is essentially validated as a boolean condition; if the result is 1, the row is inserted, if the result is 0 the row is not inserted, and an error is returned to the client.

To test this, I created your table, along with your proposed check constraint, and inserted several rows of test data:

CREATE TABLE dbo.tblTestCheckConstraint /* always specify the schema */
(
id INT,
NewColumn INT
);

ALTER TABLE tblTestCheckConstraint
ADD CONSTRAINT chk_tblTestCheckConstraint_NewColumn1 CHECK  
(
    CASE
        WHEN NewColumn >4 THEN 1
        ELSE NewColumn
    END = 1
);

INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (1);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (2);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (3);
INSERT INTO dbo.tblTestCheckConstraint (newcolumn) VALUES (5);

SELECT *
FROM dbo.tblTestCheckConstraint;

This results in several errors and allows two rows to be inserted:

enter image description here

Results:

enter image description here

When attempting to insert 2 and 3 (or in fact anything other than 1 or >4) the constraint will return false, indicating to SQL Server to not perform the insert, and return the error messages seen above.

Here is an example of how you could accomplish this using a trigger:

USE tempdb

CREATE TABLE dbo.tblTestCheckConstraint 
(
id INT CONSTRAINT PK_tblTestCheckConstring PRIMARY KEY CLUSTERED IDENTITY(1,1),
NewColumn INT NULL
);
GO
CREATE TRIGGER dbo.trg on dbo.tblTestCheckConstraint
INSTEAD OF INSERT
AS 
BEGIN
    INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
    SELECT NewColumn
    FROM INSERTED i
    WHERE i.NewColumn <=3 OR i.NewColumn IS NULL;

    INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
    SELECT 1
    FROM INSERTED i
    WHERE i.NewColumn >3;
END;
GO
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (1);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (2);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (3);
INSERT INTO dbo.tblTestCheckConstraint (NewColumn)
VALUES (4);
SELECT *
FROM dbo.tblTestCheckConstraint;

enter image description here

Ensure you remove the constraint from the table before trying to insert data, since it may conflict with the trigger code:

ALTER TABLE dbo.tblTestCheckConstraint
DROP CONSTRAINT chk_tblTestCheckConstraint_NewColumn1;

Sql-server – Nest SUM() Function Inside Case Statement

You need to remove the outer case statement. It should be:

Select sum( case when ..... then .... end) from foo

You have

Select case when sum(case when.... then .... end) end from foo

The when clause expects something that will resolve to either true or false. That's what your error message is trying to tell you.

I'm still not 100% sure what your end goal is but you either need to remove the wrapping case statement like I said above or pull the case condition out of the sum.

Select
   Case 
       When <condition > then sum(<column>)
        When <condition > then sum(<column >)
         Else sum(<column >)
         End
From foo

Best Answer

Related Solutions

Sql-server – Check Constraint with Case Statement

Sql-server – Nest SUM() Function Inside Case Statement

Related Question