SQL Insert into duplicates-CodePudding

My code is as follows:

SELECT DISTINCT
    lc.locationName,
    (SELECT SUM(c.TimeSlotsCount) FROM Schedule s 
     WHERE s.ServiceId = sv.ServiceId 
       AND sv.LocationId = lc.LocationId 
       AND sv.ServiceName LIKE '%TYPE_ONE%' 
       AND s.Date >= GETDATE()) AS TYPE_ONE_COUNT,
    (SELECT SUM(c.TimeSlotsCount) FROM Schedule s 
     WHERE s.ServiceId = sv.ServiceId 
       AND sv.LocationId = lc.LocationId 
       AND sv.ServiceName LIKE '%TYPE_TWO%' 
       AND s.Date >= GETDATE()) AS TYPE_TWO_COUNT,
    (SELECT SUM(c.TimeSlotsCount) FROM Schedule s 
     WHERE s.ServiceId = sv.ServiceId 
       AND sv.LocationId = lc.LocationId 
       AND sv.ServiceName LIKE '%TYPE_THREE%' 
       AND s.Date >= GETDATE()) AS TYPE_THREE_COUNT
FROM 
    Schedule AS sc WITH (NOLOCK) --/*This table has timeslotscount, serviceid*/
LEFT JOIN 
    ServiceList AS sv ON sv.ServiceId = sc.ServiceId --/*This table has locationid, serviceid(type1/type2/type3 depending on location[all locations has type1/2/3])*/
LEFT JOIN 
    Location AS lc ON sv.LocationId = lc.LocationId --/*This table has  locationid, locationname*/

I should explain the SUM(), there are differing dates on schedule such as

serviceId | dd/mm/yyyy hh:mm:ss | count for this day

I want to get all the "count" of "serviceId" beyond today hence the 'date >= GETDATE()'

Basically I want the table to look like so:

LOCATION | TYPE_ONE_COUNT | TYPE_TWO_COUNT | TYPE_THREE_COUNT

I am able to get the values but I get them like so:

LocationOne | 12 |  0 |  0   
LocationOne | 0  | 12 |  0  
LocationOne | 0  |  0 | 34  
LocationTwo | 1  |  0 |  0  
LocationTwo | 0  | 42 |  0  
LocationTwo | 0  | 0  |  9

Whereas I want to display as

LocationOne | 12 | 12 | 34  
LocationTwo | 1  | 42 |  9

Any and all help is appreciated. Where I'm wrong, syntax/performance improvement, references, anything.

CodePudding user response：

You can do it with conditional aggregation:

SELECT lc.locationName,
       SUM(CASE WHEN sv.ServiceName LIKE '%TYPE_ONE%' THEN sc.TimeSlotsCount ELSE 0 END) AS TYPE_ONE_COUNT,
       SUM(CASE WHEN sv.ServiceName LIKE '%TYPE_TWO%' THEN sc.TimeSlotsCount ELSE 0 END) AS TYPE_TWO_COUNT,
       SUM(CASE WHEN sv.ServiceName LIKE '%TYPE_THREE%' THEN sc.TimeSlotsCount ELSE 0 END) AS TYPE_THREE_COUNT 
FROM Location AS lc 
LEFT JOIN ServiceList AS sv ON sv.LocationId = lc.LocationId
LEFT JOIN Schedule AS sc ON sv.ServiceId = sc.ServiceId AND sc.Date >= GETDATE() 
GROUP BY lc.LocationId, lc.locationName;

CodePudding user response：

A few ways. One is to simply perform one last operation:

Fiddle

Note also: I adjusted the original SQL, since c.TimeSlotsCount does not exist. You have no c table/alias. I added TimeSlotsCount to the Schedule table. Adjust that as needed, based on the actual source of TimeSlotsCount.

WITH cte AS (
        Your above SQL from the question, without semicolon
     )
SELECT locationName
     , SUM(TYPE_ONE_COUNT)   AS TYPE_ONE_COUNT
     , SUM(TYPE_TWO_COUNT)   AS TYPE_TWO_COUNT
     , SUM(TYPE_THREE_COUNT) AS TYPE_THREE_COUNT
  FROM cte
 GROUP BY locationName
;

We could have also done a slightly different SUM in the original SQL, without the DISTINCT and with the GROUP BY, avoiding the extra CTE term.

But logically, these are the same.

Final SQL, using your SQL directly:

WITH cte AS (
      SELECT DISTINCT
          lc.locationName,
          (SELECT SUM(s.TimeSlotsCount) FROM Schedule s 
           WHERE s.ServiceId = sv.ServiceId 
             AND sv.LocationId = lc.LocationId 
             AND sv.ServiceName LIKE '%TYPE_ONE%' 
             AND s.Date >= GETDATE()) AS TYPE_ONE_COUNT,
          (SELECT SUM(s.TimeSlotsCount) FROM Schedule s 
           WHERE s.ServiceId = sv.ServiceId 
             AND sv.LocationId = lc.LocationId 
             AND sv.ServiceName LIKE '%TYPE_TWO%' 
             AND s.Date >= GETDATE()) AS TYPE_TWO_COUNT,
          (SELECT SUM(s.TimeSlotsCount) FROM Schedule s 
           WHERE s.ServiceId = sv.ServiceId 
             AND sv.LocationId = lc.LocationId 
             AND sv.ServiceName LIKE '%TYPE_THREE%' 
             AND s.Date >= GETDATE()) AS TYPE_THREE_COUNT
      FROM 
          Schedule AS sc WITH (NOLOCK) --/*This table has timeslotscount, serviceid*/
      LEFT JOIN 
          ServiceList AS sv ON sv.ServiceId = sc.ServiceId --/*This table has locationid, serviceid(type1/type2/type3 depending on location[all locations has type1/2/3])*/
      LEFT JOIN 
          Location AS lc ON sv.LocationId = lc.LocationId --/*This table has  locationid, locationname*/
     )
SELECT locationName
     , SUM(TYPE_ONE_COUNT)   AS TYPE_ONE_COUNT
     , SUM(TYPE_TWO_COUNT)   AS TYPE_TWO_COUNT
     , SUM(TYPE_THREE_COUNT) AS TYPE_THREE_COUNT
  FROM cte
 GROUP BY locationName
;