Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

Results 1 to 3 of 3
  1. #1
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,485
    Member #
    425
    Liked
    2783 times
    I've got an SQL Query where I'm trying to group a series of values together along with their quantities and costs, something like this:

    Code:
    SELECT Sum (Quantity) as Quantity, Sum (Quantity * Cost) as Item_Cost, Field_1, Field_2 Group by Field_1, Field_2
    The problem is that Field_1 has to accept null values. It groups properly by field_1, Field_2 when the value for field_1 isn't, but not when Field_1 is null. I tried concatenating the two values into one field, but that didn't work either. Anyone here know a workaround?

    Thanks.
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)

  2.  

  3. #2
    WDF Staff smoseley's Avatar
    Join Date
    Mar 2003
    Location
    Boston, MA
    Posts
    9,729
    Member #
    819
    Liked
    205 times
    Always capitalize your SQL syntax.

    Code:
    -- Using SQL Specific ISNULL() Function --
    SELECT Field_1, Field_2 
    FROM table
    GROUP BY ISNULL(Field_1, Field_2)
    
    -- Using ANSI-92 Compliant COALESCE() Function --
    SELECT Field_1, Field_2 
    FROM table
    GROUP BY COALESCE(Field_1, Field_2)
    
    -- Using ANSI-89 Compliant CASE statement --
    SELECT Field_1, Field_2 
    FROM table
    GROUP BY (CASE WHEN Field_1 IS NULL THEN Field_2 ELSE Field_1 END)
    EDIT: I added some ANSI compliant examples

  4. #3
    Unpaid WDF Intern TheGAME1264's Avatar
    Join Date
    Dec 2002
    Location
    Not from USA
    Posts
    14,485
    Member #
    425
    Liked
    2783 times
    Actually, I ended up with none of the above. Probably because I have to use an Access database. So I used the Access-only IIF function since CASE (which I had tried) was out of the question.
    If I've helped you out in any way, please pay it forward. My wife and I are walking for Autism Speaks. Please donate, and thanks.

    If someone helped you out, be sure to "Like" their post and/or help them in kind. The "Like" link is on the bottom right of each post, beside the "Share" link.

    My stuff (well, some of it): My bowling alley site | Canadian Postal Code Info (beta)


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 03:03 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com