Help with SQL

[Lxmm]

Can someone help me with the following query please?

Sample data

product date cat1 cat2 cat3 cat4 cat5
============================
00001 1/1/05 T F T T T
00001 1/1/05 T F F F T
00001 2/1/05 F T T T T
00001 2/1/05 T T F F F
00001 2/1/05 F T F F T


I want different counts of each category for a selected product. If there are more than one "T" listed in a category for the same day, I want to count that as 1.


ie. the requered results set as follows

productid cat1_count cat2_count cat3_count cat4_count cat5_count
============================================
00001 2 1 2 2 2

As you can see cat1_Count return 2 (1 for 1/1/05 and 1 for 2/1/05 though there are 2 records with "T" on 1/1/05)


Thanks in advance

 

[ZmrAbdulla]

a crosstab query?

________________________________________________________
Zameer Abdulla
Help to find Missing people
Education's purpose is to replace an empty mind with an open one.
~Malcolm S. Forbes~

 

[DanJR]

Here's one way to do it - i've only done the first 2 categories, you'll need to complete it for cat3 to cat5.

SELECT 
  t.product,
  Sum(IIf([cat1_t_num] > 0, 1, 0)) as cat1_count,
  Sum(IIf([cat2_t_num] > 0, 1, 0)) as cat2_count
FROM
  (SELECT 
     c.product, 
     Sum(IIf([cat1] = "T", 1, 0)) AS cat1_t_num, 
     Sum(IIf([cat2] = "T", 1, 0)) AS cat2_t_num
   FROM 
     CATEGORY AS c
   GROUP BY c.product, c.date) as t
GROUP BY
  t.product

your table is not setup very well, but i assume you already know that...

cheers,
dan

 

[NthDegree]

Open a query and go into SQL mode, enter the following and then run it.

select distinct iif((SELECT count(cat1) FROM table1 where cat1=true and date1=#01/01/05#)=0,0,1) as count1, iif((SELECT count(cat2) FROM table1 where cat2=true and date1=#01/01/05# )=0,0,1) as count2 from table1

Repeat this code

iif((SELECT count(catX) FROM table1 where catX=true and date1=#01/01/05#)=0,0,1) as countX

where X = to the cat number for each category count needed.

You can modify the SQL as needed for dates.

 

[Lxmm]

Many thanks for your help.