Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2002
    Location
    Texas, USA
    Posts
    2

    Question Unanswered: Conditional Count()?

    Given this (joined) table:

    ItemName SN Location

    A 1111 InLab
    A 2222 Mars
    B 3333 InLab
    B 4444 InLab
    C 5555 Venus
    C 6666 InLab
    etc.

    and SQL code:
    SELECT ItemName,
    COUNT(*) AS InLabCount
    FROM <3 tables>
    WHERE <blah blah> AND Location = 'InLab'
    GROUP BY ItemName

    I can get this table

    ItemName InLabCount

    A 1
    B 2
    C 1

    I can repeat this with "AND Location <> 'InLab' for a second table of what is out of the lab.

    But how do I change the code so that I can get the following output (i.e. display one table instead of two tables)?

    ItemName InLab OutOfLab

    A 1 1
    B 2 0
    C 1 1

    Kind regards,
    Kenneth

    (P.S. I am using MS Access with ColdFusion if that helps)

  2. #2
    Join Date
    Sep 2002
    Location
    Atlanta, GA USA
    Posts
    19

    Arrow Re: Conditional Count()?

    Something like this may do it:

    SELECT ItemName,
    SUM(IIF([Location] = 'InLab', 1, 0)) AS InLabCount,
    SUM(IIF([Location] = 'InLab', 0, 1)) As OutLabCount
    FROM <3 tables>
    WHERE <blah blah>
    GROUP BY ItemName

    Originally posted by kcoombes
    Given this (joined) table:

    ItemName SN Location

    A 1111 InLab
    A 2222 Mars
    B 3333 InLab
    B 4444 InLab
    C 5555 Venus
    C 6666 InLab
    etc.

    and SQL code:
    SELECT ItemName,
    COUNT(*) AS InLabCount
    FROM <3 tables>
    WHERE <blah blah> AND Location = 'InLab'
    GROUP BY ItemName

    I can get this table

    ItemName InLabCount

    A 1
    B 2
    C 1

    I can repeat this with "AND Location <> 'InLab' for a second table of what is out of the lab.

    But how do I change the code so that I can get the following output (i.e. display one table instead of two tables)?

    ItemName InLab OutOfLab

    A 1 1
    B 2 0
    C 1 1

    Kind regards,
    Kenneth

    (P.S. I am using MS Access with ColdFusion if that helps)

  3. #3
    Join Date
    Sep 2002
    Location
    Texas, USA
    Posts
    2

    Thumbs up Re: Conditional Count()?

    It works! Thank you! I learn something new everyday.

    Have a great day.

    Originally posted by HueyStLoui
    Something like this may do it:

    SELECT ItemName,
    SUM(IIF([Location] = 'InLab', 1, 0)) AS InLabCount,
    SUM(IIF([Location] = 'InLab', 0, 1)) As OutLabCount
    FROM <3 tables>
    WHERE <blah blah>
    GROUP BY ItemName

Posting Permissions

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