1. Registered User
Join Date
Oct 2007
Posts
34

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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

3. Registered User
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. Registered User
Join Date
Oct 2007
Posts
34
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...........

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
select Sum(columnb) as SumColumnB, count(ColumnC) as CountColumnC
from mytable
group by columnc

6. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

8. Registered User
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. Resident Curmudgeon
Join Date
Feb 2004
Location
In front of the computer
Posts
15,579
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

10. Registered User
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
•