CODEWITHSUNDEEP
sqljoin
JC97
JC97

Reputation: 1620

merge 2 rows to one on first column

I have written following query:

SELECT r.StartTime,
       (CASE d.DayOfWeekendInd WHEN 'Y' THEN COUNT(*) END) as 'WEEKDAY',
       (CASE d.DayOfWeekendInd WHEN 'N' THEN COUNT(*) END) as 'WEEKEND'
FROM t_fact_rit as r
LEFT JOIN  t_dim_date d ON r.DateId=d.DATE_SK
GROUP BY r.StartTime, d.DayOfWeekendInd
ORDER BY r.StartTime;

That results in the following:

result

How can I join the two time rows so it become one, while still keeping the other two columns?

PS Dialect is MSSQL.

Upvotes: 1

Views: 26

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You want conditional aggregation. The CASE expression is the argument to the aggregation functions, not the other way around:

SELECT r.StartTime,
       SUM(CASE WHEN d.DayOfWeekendInd = 'Y' THEN 1 ELSE 0 END) as WEEKDAY,
       SUM(CASE WHEN d.DayOfWeekendInd = 'N' THEN 1 ELSE 0 END) as WEEKEND
FROM t_fact_rit as r LEFT JOIN
     t_dim_date d 
      ON r.DateId=d.DATE_SK
GROUP BY r.StartTime
ORDER BY r.StartTime;

Upvotes: 2

Related Questions