question

pete 1 avatar image
pete 1 asked

add a sum into a field with values

I have 3 fields, p_ID, unit_type, and Units

Unit_Type has 2 different values, 'sf' and 'u'

If 'u', then the Units field is fine but if 'sf', then i would like it to count the amount of times that p_id appears.

in access we used a Sum(IIF(unit_type='sf',1,Units)) AS Total_Units

I am having trouble doing this in SQL, any thoughts?

viewaggregates
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Scot Hauder avatar image
Scot Hauder answered

SELECT SUM(CASE WHEN Unit_Type = 'u' THEN Units ELSE 1 END) [Total_Units]

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.