Hi Richard,

Why don't you create only the select ordering the producer, category and
subcategory and calculate the counts when you populate the tree?
Another option is create another select that make the sum, like this:

Select to calculate the subcategory=B4s count ("COUNT_SUBCAT 
(v_subcateg in varchar(30))"):

SELECT COUNT(subcategory_code) FROM GOODS WHERE subCategory_code 
v_subCateg

Select to calculate the category=B4s count ("COUNT_CAT (v_categ IN
varchar(30))" ):

SELECT SUM(COUNT_SUBCAT(Subcategory_code) FROM GOODS where 
Category_code = v_categ

And a Select to calculate the Producer=B4s count ("COUNT_PRODUCER 
(v_producer IN varchar(30))"):

SELECT SUM(COUNT_CAT(Category_code)) FROM GOODS where Producer_code 
v_producer

Your Select will seems like:

SELECT Producer_code, COUNT_PRODUCER(Producer_code), Category_code,
COUNT_CAT(category_code), Subcategory_code, 
count_subcat(Subcategory_code)
FROM Goods
WHERE Producer_code = 'Johnandco'
ORDER BY Producer_code, Category_code, Subcategory_code

It's something like this, you need depurate. Sorry for my english, 
I'm brazilian.

Best regards,

Juliano Moraes Rodrigues


-----Mensagem original-----
De: Bukovansky@a... [mailto:Bukovansky@a...]
Enviada em: sexta-feira, 10 de agosto de 2001 11:01
Para: sql language
Assunto: [sql_language] Counting rows specially


Hi,

I work on one crazy SQL select...

I have table Goods with columns producer_code varchar(30),
category_code varchar(30), subcategory_code varchar(30)
and of course anothers columns but they are now not important.
So now i want to make tree with this shape (just only for one
producer, not whole tree):

code_producer (count of goods for this producer)
  |
  |-- category (count of goods for this producer and category)
         |
         |-- subcategory (count of goods for this producer, category 
and
subcategory)

ie:

Bakery John & co. (11)
  |
  |-- Pastry (9)
  |     |
  |     |-- Rolls (5)
  |     |-- Croissants (4)
  |
  |-- Bread (2)
        |
        |-- With cumin (2)

So, is this produceable with one SELECT statment?
The resultant recordset:

Bakery John & co. | 11 | Pastry | 9 | Rolls      | 5
Bakery John & co. | 11 | Pastry | 9 | Croissants | 5
Bakery John & co. | 11 | Bread  | 3 | With cumin | 2

I tried the some SQL select but maximum what can I get
is

Bakery John & co. | 11 | Rolls      | 5
Bakery John & co. | 11 | Croissants | 5
Bakery John & co. | 11 | With cumin | 2

with this select:

SELECT Producer_code,
       Producer_count 
       (SELECT COUNT(Producer_code) FROM Goods WHERE Producer_code 
'ASUS'),
       Subcategory_code, Subcategory_count = COUNT(Subcategory_code)
FROM Goods
WHERE Producer_code = 'Johnandco'
GROUP BY Producer_code, Category_code, Subcategory_code

How to do with Category?

Thank you for advice. I will appreciate every help.

Regards,

Richard Bukovansky