how to think about this problem?

Question

how to think about this problem?

KoldCoffee

SSCoach

Points: 18971
More actions
October 10, 2014 at 6:57 am

#291467

(using sql server 2008R2)
I have a query that gets total messages sent and the devices they were sent to.
From this I need to enumerate two things:
(1) total messages sent
(2) total messages sent to only one device
Say this table is the output of one of my last CTEs:
create table messagesSent (message_id int, device_id int);
insert into messagesSent values
(1234, 2345),
(2342, 8903),
(2342, 8746),
(2342, 7412),
(8739, 2123),
(8739, 5738);
I need one more piece of logic. I tried to use a row_number with windows function like this
select message_id,
ROW_NUMBER() over (partition by message_id order by message_id)
from messagesSent;
I can see that message 1234 has a maximum row_number of 1, 2342 has a max row_number of 3, and message_id 8739 has max row_number of 2....
So, I thought that if the max row_number of any message_id was 1, then it represents a message sent to just one device, etc. But I can't combine Max with a windowed function.
I tried:
select message_id,
ROW_NUMBER() over (partition by message_id order by message_id),
CASE when max(ROW_NUMBER() over (partition by message_id order by message_id)) = 1
THEN 1 else 0
END as SingularReferral,
CASE when max(ROW_NUMBER() over (partition by message_id order by message_id)) > 1
THEN 1 else 0
END as MultipleReferral
from messagesSent;
Can anyone think of how to get these two totals programmatically (sql query language)?

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Try COUNT() OVER(PARTITION BY...)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

KoldCoffee SSCoach Points: 18971 More actions · Answer 2

thank you Chris!

;with cte as

(

select message_id,

count(message_id) over (partition by message_id) as NoSent

from messagesSent

)

Select

message_id,

case when NoSent = 1 then sum(NoSent) end as SentToOne,

case when NoSent > 1 then count(distinct message_id) end as SentToMany,

count(message_id) as TotalSent

from cte

group by message_id, NoSent