CODEWITHSUNDEEP
sqlsql-server-2008
PeteTheGreek
PeteTheGreek

Reputation: 729

SQL group by on one column with other columns selected

I have this function which returns the results below, but how can I remove the duplicate rows? A group by would do it but how to I build it into my function? And then also use the inner joins on the 'H' fields.

SELECT
        ACTION.holiday_id, 
        ACTION.id,
        --H.holiday_name, 
        --H.date_start,
        --H.date_end,
        (SELECT Count(DISTINCT ACTION.holiday_id) FROM ACTION) as 'Count'
FROM 
ACTION  
--INNER JOIN HOLIDAY_REF H ON ACTION.holiday_id=H.holiday_id
WHERE ACTION.delete_date is NULL
--GROUP BY ACTION.holiday_id


holiday_id id Count
31         5  3
31         6  3
31         8  3
332        7  1

So it would look like:

31  3

332 1

Upvotes: 0

Views: 34

Answers (2)

Ivan Sivak
Ivan Sivak

Reputation: 7488

This should work.

with cte as
(
select holiday_id, 
       id, 
       holiday_name, 
       date_start, 
       date_end, 
       row_number() over (partition by holiday_id order by holiday_id) as [Index]
from Action
)
select holiday_id, id, holiday_name, date_start, date_end,
       (select count(*) from Action 
        where holiday_id = cte.holiday_id and delete_date is null) as [Count]
from cte
where [Index] = 1

EDIT: Checks for delete_date as well

http://sqlfiddle.com/#!6/6d8ad/2

Upvotes: 1

dean
dean

Reputation: 355

Did you try:

SELECT
    ACTION.holiday_id,
    Count(*) [Count] 
    FROM ACTION
WHERE ACTION.delete_date is NULL
GROUP BY ACTION.holiday_id

Upvotes: 0

Related Questions