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