Results 1 to 10 of 10

Thread: Query Sorting

  1. #1
    Join Date
    Oct 2007
    Posts
    34

    Unanswered: Query Sorting

    I've got a query that has 3 columns.
    Column A has the date
    Column B has quantities (numbers)
    Column C has Letters (P or F)
    How do I sort these columns to show only the F retuns in the third column while still maintaining the completely summed quantity from Column B?

    Thanks,
    Don......

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If I understand your question correctly (and I'm not sure about that), then I'd just use a constant of "F" and not worry about Column C.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Oct 2007
    Posts
    34
    I'm not sure it was clear. I understand about using "F" but I also want the quantities summed.

    Don........

  4. #4
    Join Date
    Oct 2007
    Posts
    34
    Just thinking about this. I wasn't clear.
    I'd like to sum the quantities in Column B while at the same time count the number of "F's" in Column C

    Don...........

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    select Sum(columnb) as SumColumnB, count(ColumnC) as CountColumnC
    from mytable
    group by columnc
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Oct 2007
    Posts
    34
    Okay. If Column C has 2 attributes P or F and I wanted to sort it by F how would that SQL look?

    Don...........

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'd go for something like:
    Code:
    SELECT thingie, Sum([Column B]) AS BSum
    ,  Sum(CASE WHEN 'F' = [Column C] THEN 1 END) AS mabob
       FROM myTable
       GROUP BY thingie
    Once you discover your thingie, I can provide a more explicit version of this example. If you can include the CREATE TABLE statement for your table, that would help even more!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Oct 2007
    Posts
    34
    almost but I get this message:
    Syntax error (missing operator) in query expression 'sum(CASE When "F" =[passfail] THEN 1 END)'.

    My SQL:
    SELECT" S" AS type, sum([lotsize]) as sumoflotsize, sum(CASE When "F" = [PassFail] THEN 1 END) AS Failed
    From S
    group by PassFail;

    What am I missing?

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is "air code", but it ought to give you the right idea:
    Code:
    SELECT" S" AS type, sum([lotsize]) as sumoflotsize
    ,  sum(Iif("F" = [PassFail],1,0)) AS Failed
       FROM S
       GROUP BY PassFail;
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Oct 2007
    Posts
    34
    Thanks,
    That works very well.

    Don.......

Posting Permissions

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