April 24, 2012 at 10:08 am
Hi,
I have the following table and data. I want to make a count on each brand such that for each resp_Id i can see the frequency of Q5 as 1 or 2. 1 if the brand for the resp_Id has at least one occurrence when week is 2 or week is 4 but not both together (either). 2 when the resp_Id has at least one occurrence of the brand when week is 2 and week is 4, both.
--===== 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
)
/*resp_Id 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 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
I tried this query but it's not correct for all possible cases
SELECT resp_Id,Brand, FOP = Count(distinct Q5)
FROM #mytable
WHERE Cat=3
Group by resp_Id,Brand
Thanks
April 24, 2012 at 10:19 am
Based on the sample data, what is your expected result?
April 24, 2012 at 10:28 am
Thanks,
First i'll like start by getting to see the occurrences as explained for each brand a resp_Id has.
April 24, 2012 at 10:30 am
Don't know where to start if I don't know where to finish.
April 24, 2012 at 10:33 am
Something like this.
Resp_Id Brand Oc
1002 0 1
1002 5 1
1002 14 1
1002 17 1
But for all Resp_Id. The above is not fully correct. (Oc means Occurrence)
April 24, 2012 at 10:37 am
If you understand my explanation, then try start with the query i wrote earlier and limit to a resp_Id first and check the output. The output this query gives is not correct for all brand.
SELECT resp_Id,Brand, FOP = Count(distinct Q5)
FROM #mytable
WHERE Cat=3 and Resp_Id=1002
Group by resp_Id,Brand
April 24, 2012 at 10:38 am
oolawole (4/24/2012)
Something like this.Resp_Id Brand Oc
1002 0 1
1002 5 1
1002 14 1
1002 17 1
But for all Resp_Id. The above is not fully correct. (Oc means Occurrence)
If the expected results aren't correct, how can I be sure that what I have done is correct? You have the sample data from above, using it, put together (manually) what the final results should be. This will give us something to test our work against.
April 24, 2012 at 10:51 am
Please try these queries.
Select resp_Id,Week,Month,Cat,Brand,Q5
From #mytable
Where Cat=3 and Resp_Id=1002
SELECT resp_Id,Brand, FOP = Count(distinct Q5)
FROM #mytable
WHERE Cat=3 and Respondent_Id=1002
Group by resp_Id,Brand
On executing the two together, there should be 5 rows from the first and 4 rows from the second.
Check brand 17 from first, it appears for both week 2 and week 4; its occurrence count should be 2 not 1. For brand 14, the occurrence cont is 1 since we can see it's only in week 2.
Hope that's clearer. Even though i know how the result should be, but i can't check manually for all the resp_Id.
April 24, 2012 at 10:55 am
You could try cutting down the amount of sample data so that deriving the expected results manually would not be such a choir.
We only need enough to properly represent the problem domain.
April 24, 2012 at 12:29 pm
Agreed. This should be pretty simple but 28k+ rows is WAY too much. I let your insert for more than 5 minutes on my box and it was still going.
How about the following. Does this represent the whole problem? If so, what should the output look like?
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
)
/*resp_Id 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 '1005','2','1','11','1','3','1' UNION ALL
SELECT '1005','2','1','11','1','7','1' UNION ALL
SELECT '1005','2','1','11','3','80','1' UNION ALL
SELECT '1005','2','1','11','8','1','1' UNION ALL
SELECT '1005','2','1','11','10','1','1' UNION ALL
SELECT '1005','2','1','11','11','8','1' UNION ALL
SELECT '1007','2','1','11','1','40','1' UNION ALL
SELECT '1007','2','1','11','1','7','1' UNION ALL
SELECT '1007','2','1','11','1','3','1' UNION ALL
SELECT '1007','2','1','11','3','0','1' UNION ALL
SELECT '1007','2','1','11','3','11','1' UNION ALL
SELECT '1007','2','1','11','8','1','2' UNION ALL
SELECT '1007','2','1','11','11','20','1' UNION ALL
SELECT '1007','2','1','11','11','10','1' UNION ALL
SELECT '1007','2','1','11','11','8','1' UNION ALL
SELECT '1009','2','1','11','1','23','1' UNION ALL
SELECT '1009','2','1','11','2','12','1' UNION ALL
SELECT '1009','2','1','11','3','17','3' UNION ALL
SELECT '1009','2','1','11','11','10','2' UNION ALL
SELECT '1013','2','1','11','2','12','3' UNION ALL
SELECT '1013','2','1','11','3','1','2'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2012 at 12:40 pm
I agree with Lynn here. If you can't represent your problem with 50 or fewer records, then your problem is probably too complex to solve in a forum post.
Also, your table only has smallint and tinyint fields, so why are you using character strings for your input?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
April 24, 2012 at 1:46 pm
drew.allen (4/24/2012)
I agree with Lynn here. If you can't represent your problem with 50 or fewer records, then your problem is probably too complex to solve in a forum post.Also, your table only has smallint and tinyint fields, so why are you using character strings for your input?
Drew
took over 5 min and i canceled it to. im guessing the text into smallint and tinyint conversions are what is taking up so much time?? a question for me and not to the OP but the OP may benifit.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 24, 2012 at 1:48 pm
And the 28,000+ queries isn't exactly going to be fast. :w00t: Add to that the implicit conversion on every single column...might was well take a nap.
If we could just the OP back we could solve this in just a minute or two.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2012 at 1:54 pm
Sean Lange (4/24/2012)
And the 28,000+ queries isn't exactly going to be fast. :w00t: Add to that the implicit conversion on every single column...might was well take a nap.If we could just the OP back we could solve this in just a minute or two.
Well, those of you who took the time to actually copy and paste 28K rows, more power to you. I would have taken the time.
I am beginning to wonder if we scared the OP off? It would be nice if he would come back and help us out a bit more.
I wouldn't be surprised if this couldn't be solved quickly with a smaller set of data and the expected results.
April 25, 2012 at 2:50 am
Hi,
I will reduce the data to something we can work with, give me a moment to see the sample that can present the possible cases.
Thanks
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply