Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    3

    Question Unanswered: list use products

    hi
    I have 2 tables that are,

    Create table products (idproduct int, nameproduct varchar(80), primary key (idproduct))
    Create table use(product int, dateuse date, primary key(dateuse,product))
    what i want to do is, a select that show if a product was uses in that day, and if was, show a 1 ou X, if not dont show anything, and in the last collum show the sum.
    the date can be for a week, 2008/1/1, 2008/1/2, 2008/1/3, 2008/1/4, 2008/1/5, 2008/1/6, 2008/1/7.
    what i wanna do is like this.
    Prod_|_2008/1/1_|_2008/1/2___|_2008/1/2__|_2008/1/3_|_2008/1/3_| SUM___|
    ---------------------------------------------------------------------------|
    _1__|_________|_____X_____|_____X____|_________|__ ___X____|____3__|
    _2__|_________|_____X_____|_____X____|_________|__ ___X____|____3__|
    _3__|____X____|___________|__________|_________|__ ___X____|____2__|
    _4__|____X____|___________|_____X____|____X____|__ ___X____|____4__|
    _5__|_________|_____X_____|_____X____|_________|__ ___X____|____3__|
    _6__|____X____|___________|__________|_________|__ ___X____|____2__|
    _7__|_________|____X______|_____X____|____X____|__ ___X____|____4__|
    _8__|____X____|____X______|_____X____|_________|__ ___X____|____4__|
    hope you can help me
    cya
    Last edited by jonasp; 03-29-08 at 13:17.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT product
         , dateuse 
      FROM use
     WHERE dateuse BETWEEN '2008-01-01' AND '2008-01-07'
    ORDER
        BY product
         , dateuse
    and then format the results with your application code

    SQL should not be misused for cosmetic rearrangement, especially when this means the SQL becomes bloated, complex, and difficult to maintain
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2008
    Posts
    3
    hi

    i have something like this, almost like i want, but dont show the X

    select idproduct as 'Product'
    ,sum(CASE used.dateused WHEN '2008-01-01' THEN "X" ELSE "" END)
    ,sum(CASE used.dateused WHEN '2008-01-02' THEN "X" ELSE "" END)
    ,sum(CASE used.dateused WHEN '2008-01-03'THEN "X" ELSE "" END)
    ,sum(CASE used.dateused WHEN '2008-01-04'THEN "X" ELSE "" END)
    ,sum(CASE used.dateused WHEN '2008-01-05'THEN "X" ELSE "" END)
    ,sum(CASE used.dateused WHEN '2008-01-06'THEN "X" ELSE "" END)
    ,sum(CASE used.dateused WHEN '2008-01-07'THEN "X" ELSE "" END)
    ,count(products.idproduct) as 'Total Used'
    from products, used
    where products.idproduct =used.product
    group by products.idproduct;

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use MIN instead of SUM, and use ELSE NULL instead of ELSE ''

    remove the Total Used column for the time being (i will help you put it back in a moment)

    also, i don't think you need the products table -- could you confirm?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2008
    Posts
    3
    Quote Originally Posted by r937
    use MIN instead of SUM, and use ELSE NULL instead of ELSE ''

    remove the Total Used column for the time being (i will help you put it back in a moment)

    also, i don't think you need the products table -- could you confirm?
    no i have to use it

    now show me the X, but the days that i have 0 in the total, never been used, dont show me

    right now i have

    Code:
    select products.idproduct as 'Product'
          ,CASE used.dateused WHEN '2008-01-01' THEN"X" ELSE " " END "01/01/2008"
          ,CASE used.dateused WHEN '2008-01-02' THEN"X" ELSE " " END "02/01/2008"
          ,CASE used.dateused WHEN '2008-01-03' THEN"X" ELSE " " END "03/01/2008"
          ,CASE used.dateused WHEN '2008-01-04' THEN"X" ELSE " " END "04/01/2008"
          ,CASE used.dateused WHEN '2008-01-05' THEN"X" ELSE " " END "05/01/2008"
          ,CASE used.dateused WHEN '2008-01-06' THEN"X" ELSE " " END "06/01/2008"
          ,CASE used.dateused WHEN '2008-01-07' THEN"X" ELSE " " END "07/01/2008"
          ,count(used.product) 'Total Used'
    from products, used
    where products.idproduct =used.product
    group by products.idproduct;

Posting Permissions

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