Another Count Question

[Elroacho]

Hi Guys,

I have trawlled the forum but can't find the answer to this Count problem...

I have a querry that results in something like this:

user_id | item_id | is_member | sales_rep
------------------------------------------
123     |   aa    |      Y    |   bob
123     |   bb    |      Y    |   bob
123     |   cc    |      Y    |   bob
456     |   aa    |      N    |   bill
456     |   dd    |      N    |   bill
789     |   bb    |      Y    |   ted
789     |   dd    |      Y    |   ted
853     |   ab    |      N    |   fred
853     |   dd    |      Y    |   fred

Note: I have used distinct because I don't want to include more than one record for a customer who has bought the same item from the same sales rep on more than one occassion.

the query goes somthing like this...

SELECT distinct customer.user_id user_id,
    orders.item_id item_id,
    customer.is_member is_member,
    customer.sales_rep sales_rep
FROM
(  orders
  LEFT JOIN customer.user_id = order.user_id)
WHERE (orders.item_id = 'aa'   OR
     orders.item_id = 'bb'   OR
     orders.item_id = 'cc'   OR
     orders.item_id = 'dd')

What I am trying to figure out is how I get an extra row to show me how may items ordered by each customer as follows...

user_id | item_id | is_member | sales_rep  |  order_count
----------------------------------------------------------
123     |   aa    |      Y    |   bob      |       3
123     |   bb    |      Y    |   bob      |       3
123     |   cc    |      Y    |   bob      |       3
456     |   aa    |      N    |   bill     |       2
456     |   dd    |      N    |   bill     |       2
789     |   bb    |      Y    |   ted      |       2
789     |   dd    |      Y    |   ted      |       2
853     |   ab    |      N    |   fred     |       2
853     |   dd    |      Y    |   fred     |       2

The count would be the result of the rest of the query, therefore cust "456" might have ordered item_id "ff" but because "ff" is not in the query I don't want that included in the output or count.

I hope this makes some sense.

Any ideas?

Cheers,
Kevin.

 

[r937]

SELECT distinct
       customer.user_id
     , orders.item_id
     , customer.is_member
     , customer.sales_rep
     , ( select count(*)
           from orders
          where user_id = customer.user_id
            and orders.item_id IN
                ('aa', 'bb', 'cc', 'dd') )
          as order_count
  FROM customer
INNER
  JOIN orders
    ON order.user_id = customer.user_id
   AND orders.item_id IN
            ('aa', 'bb', 'cc', 'dd')

r937.com | rudy.ca

 

[Elroacho]

Thanks for the reponse

I can't seem to get that to work and it's probably becase I am trying to simplify the problem to much so here it the real deal...

The SQL:

SELECT distinct ANYBODYPAT.NHINO NHINO,
    ANYBODYPAT.USERID USERID,
    ANYBODYPAT.ETHCODE ETHCODE,
    ANYBODYPAT.GENCODE GENCODE,
    ANYBODYPAT.DOB DOB,
    CLASSIFICATION.READCODE READCODE,
    ANYBODYPAT.ISCAREPLUS ISCAREPLUS,
    ANYBODYPAT.SERPROVCODE SERPROVCODE,
    GEOCODE.QUINTILE DEP
FROM
(((  CLASSIFICATION
  LEFT JOIN USER ANYBODYPAT ON ANYBODYPAT.USERID= CLASSIFICATION.USERID)
  LEFT JOIN ANYBODY ANYBODYANY ON ANYBODYANY.ANYBODYID = CLASSIFICATION.USERID)
  LEFT JOIN  GEOCODE ON GEOCODE.ANYBODYID = ANYBODYPAT.USERID)
WHERE CLASSIFICATION.ROWINACTIVE = 0 AND
    (CLASSIFICATION.READCODE = 'C10.00'   OR
     CLASSIFICATION.READCODE = 'G3.00'    OR
     CLASSIFICATION.READCODE = 'G66.00'   OR
     CLASSIFICATION.READCODE = 'H3.00'    OR
     CLASSIFICATION.READCODE = 'H33.00'   OR
     CLASSIFICATION.READCODE = 'G65.00'   OR
     CLASSIFICATION.READCODE = 'G20.00'   OR
     CLASSIFICATION.READCODE = 'N330.00'  OR
     CLASSIFICATION.READCODE = 'C04.00'   OR
     CLASSIFICATION.READCODE = 'G580.00'  OR
     CLASSIFICATION.READCODE = 'E2B.00'   OR
     CLASSIFICATION.READCODE = 'C3135.00' OR
     CLASSIFICATION.READCODE = 'C324.00')
     AND
     CLASSIFICATION.WHENCLASS IS NOT NULL AND
     ANYBODYPAT.ENROLMENTFUNDINGCODE = 'F' AND
     ANYBODYPAT.ETHCODE IS NOT NULL AND
     ANYBODYPAT.GENCODE IS NOT NULL AND
     ANYBODYPAT.DOB IS NOT NULL AND
     ANYBODYPAT.GENCODE IS NOT NULL AND
     ANYBODYPAT.GENCODE <> '' AND
     ANYBODYPAT.USERID IS NOT NULL AND
     ANYBODYPAT.NHINO IS NOT NULL AND
     ANYBODYPAT.NHINO <> ''

Now this works to produce a list of users tht have a read code in the list specified in the where clause. There will be one record per user per read code. I just want to add the column that will be a count of matched read codes for each user.

Cheers,
Kevin.

 

[Elroacho]

I should point out that this is an interbase database with all it's SQL restrictions.

I tried to apply the suggested solution but got a BLR.

Cheers,
Kevin.

 

[r937]

might i enquire why you did not post the question in forum756, the InterBase forum?

r937.com | rudy.ca

 

[Elroacho]

You may enquire why I did not post the question in forum756, the InterBase forum.

The reason is, it was 1am in the morning and I forgot there was a specific forum for Interbase.

I'm not sure how/if you can move a thread.

Cheers,
Kevin.