CODEWITHSUNDEEP
sqlsql-server
Aaron
Aaron

Reputation: 1455

Modify query to group by client identifier

I have the following query.

Base query

WITH CTE (clientid, dayZero)  
AS  
-- Define the CTE query.  
(  
SELECT
    clientid,
    DATEDIFF(
        DAY,
        MIN(calendar),
        MIN(CASE 
                WHEN total = 0 
                THEN calendar 
            END)     
    ) as dayZero
FROM (
    SELECT 
        clientid, 
        CONVERT(datetime, convert(varchar(10), calendar)) calendar,
        TOTAL
    FROM STATS s1
) a
GROUP BY clientid 
),
cteb as  
-- Define the outer query referencing the CTE name.  
(SELECT cte.*, c.company, v.Name, m.id as memberid
FROM CTE
JOIN client c
on c.id = cte.CLIENTID
join Domain v
on v.Id = c.domainID
join subscriber m
on m.ClientId = c.id
join activity a
on m.id = a.memberid
where c.id != 023
),
ctec as
(
select count(distinct memberid) as Number from cteb
group by clientid
)

select clientid, dayzero, company, name, Number from cteb , ctec

The output of this query is - 

clientid  dayzero   company            name     Number
21        35       School Boards    Education   214
21        35       School Boards    Education   214

I want it to only return 1 row per client. Any ideas on how to modify this query

Sub Query

    select count(distinct memberid) as Number from cteb
    group by clientid

When I only run the query until the above subquery and select like so - 

select * from ctec
where clientid = 21

I get 

clientid   Number
21          214
22          423

This is what I would. But when I run the following select to get all the other columns I need, I start getting duplicates. The output makes sense because I am not grouping by clientid. But if I groupby how do I get the other columns I need?

select clientid, dayzero, company, name, Number from cteb , ctec

UPDATE

When I run the below select

select clientid, dayzero, company, name, Number from cteb , ctec
group by clientid, dayzero, company, name, Number

I still get 

 clientid  dayzero  company            name     Number
    21        35       School Boards    Education   214
    21        35       School Boards    Education   215

I don't understand why I am getting different numbers in the Number column (214 and 215 in this case). But when I run it with the group by as shown below, I get the correct numbers.

    select count(distinct memberid) as Number from cteb
    group by clientid

    select * from ctec
    where clientid = 21

I get 

clientid   Number
21          2190

Neither 214 nor 215 is correct. The correct number is 2190 which I get when I groupby as shown above.

Upvotes: 2

Views: 131

Answers (1)

PSK
PSK

Reputation: 17953

If you want to show unique rows based on a particular column, you can use ROW_NUMBER() like following query.

select * from
(
select clientid, dayzero, company, name, Number, 
        ROW_NUMBER() OVER(PARTITION BY clientid ORDER BY Number DESC) RN
 from cteb , ctec
) t
where RN=1

Upvotes: 1

Related Questions