sqlgroup-bycase

GROUP BY is not working with multiple Case Statements and a Left Join

I have two tables I am trying to write a GROUP BY clause with multiple CASE statements so that the data will be grouped into one row for each client instead of multiple rows for each client.

Here is a quick glance of the tblClient and tblServices table:

tblClient

tblServices

These are my current results:

Current Results

Here are my desired results:

Desired Results

I wrote two queries and the both give me the current results and not my desired results:

SELECT 
    c.ClientID, CreateDate,
    MAX (CASE WHEN c.ServiceID = 1 THEN 1 ELSE 0 END) AS ‘Kitchen’,
    MAX (CASE WHEN c.ServiceID = 2 THEN 1 ELSE 0 END) AS ‘Living Room’,
    MAX (CASE WHEN c.ServiceID = 3 THEN 1 ELSE 0 END) AS ‘Dining Room’,
    MAX (CASE WHEN c.ServiceID = 4 THEN 1 ELSE 0 END) AS ‘Family Room’
FROM 
    tblClient c 
LEFT JOIN 
    s.tblServices s ON s.ServiceID = s.ServiceID
GROUP BY
    c.ClientID, CreateDate, c.ServiceID 

SELECT 
    c.ClientID, CreateDate,
    (CASE WHEN c.ServiceID = 1 THEN 1 ELSE 0 END) AS ‘Kitchen’,
    (CASE WHEN c.ServiceID = 2 THEN 1 ELSE 0 END) AS ‘Living Room’,
    (CASE WHEN c.ServiceID = 3 THEN 1 ELSE 0 END) AS ‘Dining Room’,
    (CASE WHEN c.ServiceID = 4 THEN 1 ELSE 0 END) AS ‘Family Room’
FROM 
    tblClient c 
LEFT JOIN 
    s.tblServices s ON s.ServiceID = s.ServiceID
GROUP BY
    c.ClientID, CreateDate, 
    CASE WHEN c.ServiceID = 1 THEN 1 ELSE 0 END,
    CASE WHEN c.ServiceID = 2 THEN 1 ELSE 0 END, 
    CASE WHEN c.ServiceID = 3 THEN 1 ELSE 0 END,
    CASE WHEN c.ServiceID = 4 THEN 1 ELSE 0 END

I am using SQL Server.

Solution

  • Summary from the request comments: If you want one result row per client and date, then group by client and date.

    SELECT 
    c.clientid, c.createdate,
    MAX (CASE WHEN c.serviceid = 1 THEN 1 ELSE 0 END) AS "Kitchen",
    MAX (CASE WHEN c.serviceid = 2 THEN 1 ELSE 0 END) AS "Living Room",
    MAX (CASE WHEN c.serviceid = 3 THEN 1 ELSE 0 END) AS "Dining Room",
    MAX (CASE WHEN c.serviceid = 4 THEN 1 ELSE 0 END) AS "Family Room"
FROM tblclient c 
GROUP BY c.clientid, c.createdate
ORDER BY c.clientid, c.createdate;

    If you rather want one result row per clientid only, then GROUP BY c.clientid and either select MIN(c.createdate) or MAX(c.createdate) or no date at all.