Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2011
    Posts
    9

    Unanswered: Query help to count/sum from another column based on a condition

    Let's assume I have a table that looks like this

    Name Type Total Type
    Mike Blue
    Fred Blue
    John Orange
    Tom Orange
    Bill Orange

    How do I write an expression in the query column so that the "total type" will count the type and put that count only where that type is.

    It should look like this:

    Name Type Total Type
    Mike Blue 2
    Fred Blue 2
    John Orange 3
    Tom Orange 3
    Bill Orange 3

    I know it's some kind of IIF with a sum but I can't figure it out. Thanks for the help.

  2. #2
    Join Date
    Aug 2011
    Posts
    9
    The resulting table did not space out correctly when I posted it. The Total type column has the numbers in it.

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Like this:
    Code:
    SELECT t.Name,t.Type, c.TotalType
    FROM Table1 AS t LEFT JOIN (
        SELECT Table1.Type, COUNT(Table1.Type) AS TotalType
        FROM Table1
        GROUP BY Table1.Type
    ) AS c ON t.Type = c.Type
    Have a nice day!

  4. #4
    Join Date
    Aug 2011
    Posts
    9
    Thanks. That was fast.

    Is there a way to do it without creating another table? I gave some simple data but the table I am working with has 57 columns and I'm not sure how I would rewrite your sql with all of that.

  5. #5
    Join Date
    Aug 2011
    Posts
    9
    Here what I tried so far. If [flex field 1] is a specialist then count how many specialists in all the rows in the table and put the count of them here. Otherwise it's a zero.


    =Company Employees Count: IIf([Flex Field 1]="specialist",Sum(IIf([Flex Field 1]="Specialist",1,0)),0)

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No table is created in the example I provided. I simply use a subquery on the same table with a GROUP BY clause in it. This is necessary because when you use a GROUP BY clause, all columns in the query must be part of an aggregate function (SUM, COUNT, MIN, MAX, etc.), or must be in the GROUP BY clause.

    The number of columns does not matter really: there is still one column that is present in both the main query and the subquery with the GROUP BY clause and that is used to perform the equality in the join. In this case it was Type, that you can have in the subquery without an aggregate function because it is the column used for grouping by.

    I'm not sure to understand what you try to do with the IIf() function in your expression: Sum ignores the records with Null values in the summed field and always returns a numeric value, be it zero.
    Have a nice day!

  7. #7
    Join Date
    Aug 2011
    Posts
    9
    Thanks again. I implemented your solution and it worked great.

    I really appreciate you taking the time to help.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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