CODEWITHSUNDEEP
sqlsql-servergroup-bycommon-table-expression
Csibi Norbert
Csibi Norbert

Reputation: 930

GROUP BY brings duplicates and it`s not grouping

I'm trying to GROUP BY every Authority id in a row by date and Off/OnStreet. I don't understand what is going wrong in the SQL query. It brings me duplicates or repetitive AuthorityId instead of grouping everything by authority ID base on date and On/Offstreet.

enter image description here

And this is my SQL query

WITH ParkeonCTE
AS
(
SELECT 
    OccDate = CONVERT(DATE, LocalStartTime),
    TotalOccSessions = COUNT(SessionId),
    AuthorityId,
    (CASE
        WHEN OC.OspId IS NULL THEN 'OffStreet' ELSE 'OnStreet'
        END
    ) AS ParkingContextType
FROM Analytics.OccupancySessions AS OC
WHERE AuthorityId IS NOT NULL
GROUP BY  CONVERT(DATE,LocalStartTime), AuthorityId, OspId
)

  SELECT CONVERT(DATE,OC.OccDate),
   OC.TotalOccSessions,
   OC.ParkingContextType,
    OC.AuthorityId
  FROM ParkeonCTE AS OC
  GROUP BY CONVERT(DATE, OC.OccDate), OC.AuthorityId, OC.TotalOccSessions, OC.ParkingContextType
  ORDER BY CONVERT(DATE,OC.OccDate) DESC

Upvotes: 0

Views: 68

Answers (2)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

Use Aggregate function SUM()

WITH ParkeonCTE
AS
(
SELECT 
    OccDate = CONVERT(DATE, LocalStartTime),
    TotalOccSessions = COUNT(SessionId),
    AuthorityId,
    (CASE
        WHEN OC.OspId IS NULL THEN 'OffStreet' ELSE 'OnStreet'
        END
    ) AS ParkingContextType
FROM Analytics.OccupancySessions AS OC
WHERE AuthorityId IS NOT NULL
GROUP BY  CONVERT(DATE,LocalStartTime), AuthorityId, OspId
)

   SELECT 
      CONVERT(DATE,OC.OccDate),
      SUM(OC.TotalOccSessions),
      OC.ParkingContextType,
      OC.AuthorityId
  FROM ParkeonCTE AS OC
  GROUP BY CONVERT(DATE, OC.OccDate), OC.AuthorityId, OC.ParkingContextType
  ORDER BY CONVERT(DATE,OC.OccDate) DESC

Upvotes: 1

LukStorms
LukStorms

Reputation: 29647

The outer query is grouping on 1 too many.

...
SELECT 
 CONVERT(DATE,OC.OccDate) AS OccDate,
 SUM(OC.TotalOccSessions) AS TotalOccSessions,
 OC.ParkingContextType,
 OC.AuthorityId
FROM ParkeonCTE AS OC
GROUP BY CONVERT(DATE, OC.OccDate), OC.AuthorityId, OC.ParkingContextType
ORDER BY CONVERT(DATE, OC.OccDate) DESC

Upvotes: 1

Related Questions