SQL Help

Discussion in 'OT Technology' started by Brock, Dec 8, 2005.

  1. Brock

    Brock New Member

    Joined:
    Sep 7, 2004
    Messages:
    5,791
    Likes Received:
    0
    Location:
    San Diego, CA
    I don't do sql much so I need a little help with something that maybe be fairly easy.

    table1: id1, status
    table2: id2, id1, quantity, group

    I need a query that will give me 2 pieces of info, the count of table1.id1 with a particular status, and the SUM of table2.quantity within a particular group. Something like

    SELECT COUNT(DISTINCT table1.id1) AS MyCount, SUM (table2.quantity) AS TotalQuantity
    FROM table1
    INNER JOIN table2 ON table1.id1 = table2.id1
    WHERE (table1.status = 1) AND (table2.group = 2)

    This doesn't work because I want the COUNT of all table1.id1's where status = 1, regardless of table2.group.

    I also want the SUM of table2.quantity WHERE (table1.status = 1) AND (table2.group = 2).
     
  2. Penguin Man

    Penguin Man Protect Your Digital Liberties

    Joined:
    Apr 27, 2002
    Messages:
    21,696
    Likes Received:
    0
    Location:
    Edmonton, AB
    Well the two pieces of data you want aren't actually related to each other in any way, right? So, what you really want is either:
    a) two separate queries or
    b) to setup a view that gives both pieces of info and then do one query on the view.

    Code:
    CREATE VIEW myview(countoft1, sumoft2) AS (
      SELECT COUNT(DISTINCT newt1.id1) AS countoft1, SUM(newt2.quantity) AS sumoft2
      FROM (
        SELECT *
        FROM table1
        WHERE status = 1
      ) AS newt1, (
        SELECT *
        FROM table1 JOIN table2 ON table1.id1 = table2.id2
        WHERE table2.group = 2
          AND table1.status = 1
      ) as newt2
    );
    Something like that anyway, my view-creating skills are a little rusty and I don't 100% understand what the data is you're looking for.
     
  3. Brock

    Brock New Member

    Joined:
    Sep 7, 2004
    Messages:
    5,791
    Likes Received:
    0
    Location:
    San Diego, CA
    I found a way to do it using a CASE stmt:

    Code:
    SELECT COUNT(DISTINCT table1.id1) AS MyCount,
           SUM( CASE WHEN table2.group = 2 THEN table2.quantity
                 ELSE 0 END) AS TotalQuantity
    FROM table1
    INNER JOIN table2 ON table1.id1 = table2.id1
    WHERE (table1.status = 1)
    
    Thanks for the help.
     

Share This Page