can't solve one query!! please help, tricky one

mpasha · #1 (**permalink**) 09-02-09, 06:41

helo... i have a problem with some tables and getting data from them... hope some one can help me...

ok i have two table

TABLE A:
-id
-product_number
-costs

TABLE B:
-id
-product_number
-typ_of_product

the two product_number are the same...

now the idea si this:

i need to get all the typ_of_product which made in TABLE A most costs ORDERED BY DESC and GROUPED BY typ_of_product

the problem is the product_numer 's are uniqe but they belong to some typ and i can't order and group them by typ looking how much costs they make

hope someone can help me, because i am thinking for hours now and can't get the right query for this...

mpasha · #2 (**permalink**) 09-02-09, 08:05

ok i have solved it thanks anyway:

CREATE VIEW temporary AS SELECT `produkt_numer` as numer, `typ_of_product` as typ,`costs` as s
FROM `tableA` , `tableB`
WHERE `tableA.product_number` = `tableB.produkt_number`;

select count(numer) as howmuch, typ from temporary group by typ order by homuch desc;

r937 · #3 (**permalink**) 09-02-09, 09:52

i'm glad you solved it, but i should like to point out that ...

1) `tableA.product_number` and `tableB.produkt_number` are invalid (you can't put backticks around the qualified columns like that)

2) you don't actually need a view

Code:

SELECT COUNT(*) AS howmuch
     , typ 
  FROM ( SELECT tableA.product_number AS numer
              , tableB.typ_of_product AS typ
              , tableA.costs AS s
           FROM tableA 
         INNER
           JOIN tableB
             ON tableB.produkt_number = tableA.product_number
       ) AS temporary 
GROUP 
    BY typ 
ORDER
    BY homuch DESC

Internet Services

Web Development

Digital Marketing

Consumer Tech