SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Washington DC
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SQL question: Grouping columns

    ------------------------------------
    model | type | cost
    ------------------------------------
    123 | A | $10
    123 | B | $20
    456 | A | $50
    456 | B | $100


    I'm trying to get a query to return this:

    -------------------------------------------
    model | cost_A | cost_B
    -------------------------------------------
    123 | $10 | $20
    456 | $50 | $100



    any help? thanks.

  2. #2
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    An inner join might work here:

    Code:
    SELECT a.model AS model, a.cost AS cost_a, b.cost AS cost_b
       FROM products AS a
    INNER
       JOIN products AS b
          ON a.model = b.model
    WHERE
       a.type = 'A'
    AND
       b.type = 'B';
    Be interested to see if there's a better way.

    EDIT: swampBoogie is right about using single vs. double quotes so I changed mine to single. :-)
    Last edited by samsm; Jun 23, 2004 at 09:23.
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  3. #3
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    698
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    SELECT a.model, 
    sum(case type when 'A' then cost else 0 end)  AS cost_a, 
    sum(case type when 'B' then cost else 0 end)  AS cost_b
       FROM products
    WHERE type in ('A','B')
    You shouldn't use " as string delimiters in SQL.

  4. #4
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the heads up about single quotes, swampBoogie, I should know better than that.

    Your query looks interesting, but I can't seem to get it to work.

    #1109 - Unknown table 'a' in field list
    #1140 - Mixing of GROUP columns (MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no GROUP BY clause
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Washington DC
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks guys, although I can't get either to work.

    samsm: if a "model" doesn't have records for both types, then the query won't display the record at all

    ex:

    ------------------------------------
    model | type | cost
    ------------------------------------
    123 | A | $10
    123 | B | $20
    456 | A | $50


    I'm trying to get a query to return this:

    -------------------------------------------
    model | cost_A | cost_B
    -------------------------------------------
    123 | $10 | $20
    456 | $50 | $0

    instead, i'm getting this:
    -------------------------------------------
    model | cost_A | cost_B
    -------------------------------------------
    123 | $10 | $20

  6. #6
    SitePoint Wizard samsm's Avatar
    Join Date
    Nov 2001
    Location
    Atlanta, GA, USA
    Posts
    5,011
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    SwampBoogie's query seems to work perfectly if you add an alias to the tablename and a group by.
    Code:
    SELECT a.model, 
     sum( CASE TYPE WHEN 'A' THEN cost ELSE 0 END ) AS cost_a, 
     sum( CASE TYPE WHEN 'B' THEN cost ELSE 0 END ) AS cost_b
    FROM products AS a
    WHERE TYPE IN ( 'A', 'B' )
    GROUP BY a.model
    Using your unpaid time to add free content to SitePoint Pty Ltd's portfolio?

  7. #7
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Washington DC
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I should have mentioned before, but I'm using Oracle.
    So, the line:
    sum( CASE TYPE WHEN 'A' THEN cost ELSE 0 END )

    is throwing an error.

  8. #8
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Washington DC
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    got it. should be like this for oracle:

    sum( CASE WHEN type = 'A' THEN cost ELSE 0 END ) AS cost_a

    thanks guys!

  9. #9
    The doctor is in... silver trophy MarcusJT's Avatar
    Join Date
    Jan 2002
    Location
    London
    Posts
    3,509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is called a PIVOT, and is a common operation, but unfortunately there is no native SQL7/2000 support for it, so it has to be done as above.

    However there will be new features to support these operations the forthcoming new version of MSSQL (codenamed "Yukon")!
    MarcusJT
    - former ASP web developer / former SPF "ASP Guru"
    - *very* old blog with some useful ASP code

    - Please think, Google, and search these forums before posting!


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
  •