SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    Original Gangster silver trophy Thing's Avatar
    Join Date
    Oct 2000
    Location
    Philadelphia, PA
    Posts
    4,708
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    multiple record query return

    Here is my query:

    Code:
    SELECT o.orderid, o.po_id, ((SUM(op.quantity) / 3) / 4) as itemtotal, COUNT(*) as counter
    FROM orders o 
    INNER JOIN order_products op ON o.OrderID = op.OrderID
    INNER JOIN applications a ON op.app_ID = a.app_id 
    INNER JOIN products p ON a.prodcode = p.prodcode
    WHERE o.orderid = 'CC1098179'
    GROUP BY  o.orderid, o.po_id
    Ok so this will return one record similar to this:

    Order ID | PO ID | Item Total | Counter
    343242 | 34234 | 4 <NULL>


    What I want to happen is return as many records as [Item Total] equals. So in this case it would return

    Order ID | PO ID | Item Total | Counter
    343242 | 34234 | 4 | 1
    343242 | 34234 | 4 | 2
    343242 | 34234 | 4 | 3
    343242 | 34234 | 4 | 4


    Possible or not?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    very possible

    what is that weird sum formula?

    what is the absolute largest number that this formula will yield?

    you will need that many rows in an integers table

    create table integers (i integer);
    insert into integers (i) values (0);
    insert into integers (i) values (1);
    insert into integers (i) values (2);
    insert into integers (i) values (3);
    insert into integers (i) values (4);
    insert into integers (i) values (5);
    insert into integers (i) values (6);
    insert into integers (i) values (7);
    insert into integers (i) values (8);
    insert into integers (i) values (9);

    p.s. and what database is this?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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