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

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

  2. #2
    Join Date
    Sep 2009
    Posts
    2

    solved

    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
    Location
    Toronto, Canada
    Posts
    20,002
    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
    rudy.ca | @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
  •