Select IF another fields value is a specific string

Question

I have an application that automatically tallies up the time spent for each user. Each user

Here is the query

SELECT
  ServiceBase.User
  ,ServiceBase.Time
  ,ServiceBase.BillingType
from ServiceBase

And here is the output

User 1  | Time
User 2  | Time
User 3  | Time
        | Total Time

I'm looking to add another column that splits the Time based on a BillingType without editing the actual application code. It will read in a new select field and automatically tally the values, so Im hoping to just use an IF inside the query to get my results to show in the application.

Something along the lines of:

SELECT
  ServiceBase.User
  ,ServiceBase.Time
  ,ServiceBase.BillingType
  ,Select If(ServiceBase.BillingType = 'Fixed'){ echo ServiceBase.Time; }else{ echo 0;}
from ServiceBase

To get a result like so:

User 1  | Time           | Fixed Time
User 2  | Time           | Fixed Time
User 3  | Time           | Fixed Time
        | Total Time     | Total Fixed Time

How can I do this in SQL?

Greg Viers · Accepted Answer · 2018-02-15 20:15:26Z

3

What you need is CASE for IFs within a SELECT statement:

SELECT
  ServiceBase.User
  ,ServiceBase.Time
  ,ServiceBase.BillingType
  ,CASE WHEN ServiceBase.BillingType = 'Fixed' THEN ServiceBase.Time ELSE 0 END AS [Fixed Time]
FROM ServiceBase

edited Feb 15, 2018 at 20:15

answered Feb 15, 2018 at 20:14

Greg Viers

3,5233 gold badges22 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

pmbAustin Over a year ago

You need to remove the extra "SELECT" before the CASE

Greg Viers Over a year ago

True. Thanks @pmbAustin.

Radim Bača Over a year ago

@MrLahey the best way to say thank you on SO is to accept the answer ;)

Radim Bača · Accepted Answer · 2018-02-15 20:14:57Z

3

Use CASE

SELECT
  ServiceBase.User
  ,ServiceBase.Time
  ,ServiceBase.BillingType
  ,CASE WHEN ServiceBase.BillingType = 'Fixed' THEN ServiceBase.Time ELSE 0 END
from ServiceBase

answered Feb 15, 2018 at 20:14

Radim Bača

10.7k1 gold badge21 silver badges35 bronze badges

Comments

Brandon Kearby · Accepted Answer · 2018-02-15 20:17:53Z

3

You need to use the case when construct

SELECT
  ServiceBase.User
  ,ServiceBase.Time
  ,ServiceBase.BillingType
  ,case when ServiceBase.BillingType = 'Fixed' then ServiceBase.Time else 0 end
from ServiceBase

answered Feb 15, 2018 at 20:17

Brandon Kearby

6037 silver badges8 bronze badges

Collectives™ on Stack Overflow

Select IF another fields value is a specific string

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related