Occurrence count

Question

Post reply

Occurrence count

Viewing 11 posts - 16 through 25 (of 25 total)

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

oolawole SSC Veteran Points: 285 More actions · Answer 1

Hi,

I think we can proceed with this.

--===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL

DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable

(

Resp_Id smallint,

Week tinyint,

Month tinyint,

Year tinyint,

Cat tinyint,

Brand tinyint,

Q5 tinyint

)

/*Respondent is not a primary key*/

-===== Insert the test data into the test table

INSERT INTO #mytable

(Resp_Id,Week,Month,Year,Cat,Brand,Q5)

SELECT 1002,2,1,11,1,1,2 UNION ALL

SELECT 1002,2,1,11,3,17,1 UNION ALL

SELECT 1002,2,1,11,3,14,1 UNION ALL

SELECT 1004,2,1,11,1,7,1 UNION ALL

SELECT 1004,2,1,11,1,1,1 UNION ALL

SELECT 1004,2,1,11,3,11,1 UNION ALL

SELECT 1004,2,1,11,3,75,3 UNION ALL

SELECT 1004,2,1,11,11,10,1 UNION ALL

SELECT 1004,2,1,11,11,8,1 UNION ALL

SELECT 1002,4,1,11,1,2,2 UNION ALL

SELECT 1002,4,1,11,2,12,1 UNION ALL

SELECT 1002,4,1,11,3,17,1 UNION ALL

SELECT 1002,4,1,11,3,5,3 UNION ALL

SELECT 1002,4,1,11,3,0,1 UNION ALL

SELECT 1002,4,1,11,8,1,1 UNION ALL

SELECT 1002,4,1,11,10,1,1 UNION ALL

SELECT 1002,4,1,11,11,13,1 UNION ALL

SELECT 1004,4,1,11,1,23,2 UNION ALL

SELECT 1004,4,1,11,3,75,1 UNION ALL

SELECT 1004,4,1,11,3,4,1 UNION ALL

SELECT 1004,4,1,11,8,1,2 UNION ALL

SELECT 1004,4,1,11,11,10,1

Sorry for putting that large amount, but i hope this will be enough to explain what i want.

Here's the query i wrote but does not give all i want.

Select resp_Id,Week,Month,Cat,Brand,Q5

From #mytable

Where Cat=11 and Month=1 and Resp_Id=1004

SELECT resp_Id,Brand, FOP = Count(distinct Q5)

FROM #mytable

WHERE Cat=11 and [Month]=1 and Resp_Id=1004

Group by resp_Id,Brand

The first query shows what we have for resp_Id 1004 in cat=11. The second query is to get what i want which should actually be

resp_Id Brand FOP

1004 8 1

1004 10 2

Since for brand=8, there is only one occurrence with week=2 but for brand=10, there is at least one occurrence for both week=2 and week=4 (so it's 2 as i have written above and not 1 that my query is giving).

This is where i need help.

vinu512 SSCoach Points: 15729 More actions · Answer 2

You need to replace your query:

SELECT resp_Id,Brand, FOP = Count(distinct Q5)

FROM #mytable

WHERE Cat=11 and [Month]=1 and Resp_Id=1004

Group by resp_Id,Brand

with this query, for the result you want:

SELECT resp_Id,Brand, FOP = Count(Q5)

FROM mytable

WHERE Cat=11 and [Month]=1 and Resp_Id=1004

Group by resp_Id,Brand

Distinct Count will only give 1. If you want 2 then you have to remove the Distinct.

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

oolawole SSC Veteran Points: 285 More actions · Answer 3

Thanks for that, but it will not solve it. I'll explain again.

For each of the resp_Id, i want a count on each brand (that the resp_Id has) in this way. If for a resp_Id on a brand, there is at least an occurrence for week=2, then the count is 1 (even if there are more than 1 occurrences with week=2). If there is also at least an occurrence of that same brand for week=4, then the count now becomes 2 (even if there are more than 1 occurrences with week=4). If the occurrence (or occurrences) was only on week=2 or week=4, either but not both, then the count is 1.

Try change the value of resp_Id and cat when running both queries i wrote and compare what comes out with my explanation then you should see why i am stuck.

vinu512 SSCoach Points: 15729 More actions · Answer 4

Does this help?

;With CTE

As

(Select *, ROW_NUMBER() Over (Partition By Brand, Week order by Brand) As rownum From mytable)

Select resp_Id,Brand, FOP = Count(Q5) From CTE Where rownum = 1

AND Cat=11 and [Month]=1 and Resp_Id=1004

Group by resp_Id,Brand

Vinu Vijayan

For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉

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

oolawole (4/25/2012)
Thanks for that, but it will not solve it. I'll explain again.
For each of the resp_Id, i want a count on each brand (that the resp_Id has) in this way. If for a resp_Id on a brand, there is at least an occurrence for week=2, then the count is 1 (even if there are more than 1 occurrences with week=2). If there is also at least an occurrence of that same brand for week=4, then the count now becomes 2 (even if there are more than 1 occurrences with week=4). If the occurrence (or occurrences) was only on week=2 or week=4, either but not both, then the count is 1.
Try change the value of resp_Id and cat when running both queries i wrote and compare what comes out with my explanation then you should see why i am stuck.

Try this:

SELECT resp_Id, Brand, [Year], [Month], FOP = COUNT(*)

FROM (

SELECT resp_Id, Brand, [Year], [Month], [Week]

FROM #mytable

WHERE [Week] in (2,4)

GROUP BY resp_Id, Brand, [Year], [Month], [Week]

) d

GROUP BY resp_Id, Brand, [Year], [Month]

^{“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

oolawole SSC Veteran Points: 285 More actions · Answer 6

oolawole

SSC Veteran

Points: 285

April 25, 2012 at 6:58 am

#1478933

Sorry, that does not give the desired output.

Mark Cowne One Orange Chip Points: 26969 More actions · Answer 7

Still not really clear what you're asking for

SELECT resp_Id, Brand, [Year], [Month], FOP = MAX(CASE WHEN [Week]=2 THEN 1 ELSE 0 END) +

MAX(CASE WHEN [Week]=4 THEN 1 ELSE 0 END)

FROM #mytable

--WHERE Cat=11 and [Month]=1 and Resp_Id=1004

GROUP BY resp_Id, Brand, [Year], [Month]

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

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

oolawole (4/25/2012)
Sorry, that does not give the desired output.

Why not post the desired output. All of it, from your sample data set.

^{“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

oolawole SSC Veteran Points: 285 More actions · Answer 9

oolawole

SSC Veteran

Points: 285

April 25, 2012 at 7:20 am

#1478952

Hi,

ChrisM, it worked.

Thanks everyone.

drew.allen SSC Guru Points: 76998 More actions · Answer 10

The problem is that you're trying to do it all with one function, but you need separate functions for each week.

SELECT Resp_ID, Brand

,COUNT(DISTINCT CASE WHEN [Week] = 2 THEN Q5 END)

+COUNT(DISTINCT CASE WHEN [Week] = 4 THEN Q5 END)

FROM #myTable

WHERE Cat = 11

AND [Month] = 1

AND Resp_ID = 1004

GROUP BY Resp_ID, Brand

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

oolawole SSC Veteran Points: 285 More actions · Answer 11

Hi Ten centuries,

The query you wrote did worked. But if i try extend it for week=6 and 8, it didn't. But the one ChrisM wrote did what i wanted even when extended. Still i appreciate your effort. And if you still come up with something else, i will definitely still use it.

Thanks