Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009

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

    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:

    TABLE B:

    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...

  2. #2
    Join Date
    Sep 2009


    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;

  3. #3
    Join Date
    Apr 2002
    Toronto, Canada
    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
    SELECT COUNT(*) AS howmuch
         , typ 
      FROM ( SELECT tableA.product_number AS numer
                  , tableB.typ_of_product AS typ
                  , tableA.costs AS s
               FROM tableA 
               JOIN tableB
                 ON tableB.produkt_number = tableA.product_number
           ) AS temporary 
        BY typ 
        BY homuch DESC | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts