How can I do a count ?

[goranm]

Hello !

Can anyone tell me how to deal with this:

I have two tables (ORDERROW and ARTICLE).

Articles are divided into five different groups.

I have today an SQL which counts the number of orders
for each group:

SELECT
A.ARTGROUP,
COUNT(DISTINCT B.COID)
FROM
ART A,
ORDERROW B
WHERE
A.ARTID = B.ARTID
GROUP BY
A.ARTGROUP

Now my problem is that I have to count the numbers of
orders where every orderline belongs to a specific ARTGROUP (ARTGROUP = '5')

Can this be done ?

/Goran

 

[BillDHS]

I am not sure I understand exactly, but you may get your answer by adding a HAVING clause after the WHERE:

HAVING ARTGROUP = '5'

 

[goranm]

Ok,

Thanks for your answer, but then I will get the number of orders containing an article with group = '5'.
(these orders can also include articles with other groups).

What I want is just the number of orders that contains rows
where every row have an article belonging to group = '5'.

Hope you understand.

/Goran

 

[swampBoogie]

SELECT COUNT(DISTINCT B.COID)
FROM ART A, ORDERROW B
WHERE A.ARTID = B.ARTID
and not exists (select * 
  from orderrow , art 
 where orderrow.id = b.id
  and orderrow.artid = art.artid
  and art.artgroup <> 5)

 

[dbtoo2001]

Have you experimented with cube or rollup?

SELECT
A.ARTGROUP,
COUNT(DISTINCT B.COID)
FROM
ART A,
ORDERROW B
WHERE
A.ARTID = B.ARTID
GROUP BY
cube (A.ARTGROUP)

 

[goranm]

Hello !

Back to a new week.

Thank you both for your answers.

svampBoogie:
I think I can achieve what I want with your input (with just a little modification).

dbtoo2001:
Unfortunately I couldn´t test your suggestion because we only have Oracle 7.3.4 and when I searched, I saw that these functions came with ver. 8.i.

/Goran