Madan Gehlot Madan Gehlot - 3 months ago 14
SQL Question

Split SQL column values and group by date and return single row

I have SQL Server query , using this , I am splitting event id sum columns to two columns based on some condition. Query executed successfully, but the result is not desired. It's half useful. Please help me to get expected result. I want one row for both split columns instead two rows and empty spaces.
SQL Query:

select convert(date, paymenttime)) , SUM(case when eventid = 33 then 1 ELSE 0 END) AS column1,
SUM(case when eventid = 36 then 1 ELSE 0 END) AS column2
from tbltransMain_backup where
paymentime <= '20160731' and PaymentTime >= '20160701'
group by convert(date,paymenttime),event_id
order by convert(date,paymenttime)


Result view:
SQL Result View

Expected Result:

2016-07-01 27 1
2016-07-02 28 2
2016-07-03 30 15

Answer

The query you posted (perhaps unknowingly) into your question should already give you the desired results:

SELECT CONVERT(DATE, paymenttime),
       SUM(CASE WHEN event_id = 33 THEN 1 ELSE 0 END) AS column1,
       SUM(CASE WHEN event_id = 36 THEN 1 ELSE 0 END) AS column2
FROM tbltransMain_backup
WHERE paymentime <= '20160731' AND
      paymentime >= '20160701'
GROUP BY CONVERT(DATE, paymenttime)
ORDER BY CONVERT(DATE, paymenttime)

The reason you were getting two rows for every date is that your query had the following grouping:

GROUP BY CONVERT(DATE, paymenttime),
         event_id

In other words, each date would have two groups, one for event_id = 33 and one for event_id = 36.

Comments