Thanks so much, that did the job very well.

To make it easy then, I will say amt is amount, acc is an account, smp is a sample, and regn is a region. The table His is for history,...

I have corrected the errors and even tested it

Hi all.

Sorry for the errors, I have corrected them now in this.

CREATE TABLE Regns

(

sn INT NOT NULL

...

Sorry for the errors.

SET IDENTITY_INSERT Regns ON

INSERT INTO Regns (sn,Regn)

SELECT '1','La' UNION ALL

SELECT '2','Be' UNION ALL

SELECT '3','Ib' UNION ALL

SELECT '4','En'

SET IDENTITY_INSERT Regns ON

SET IDENTITY_INSERT PJs ON

INSERT INTO PJs (sn,acc,pj)

SELECT...

What of representing a table with a foreign key?

Thanks

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...

Hi,

ChrisM, it worked.

Thanks everyone.

Sorry, that does not give the desired output.

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....

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

...

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

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...

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...