Adding another column based on different criteria (SQL-server)

Question

I do quite a bit of data analysis and use SQL on a daily basis but my queries are rather simple, usually pulling a lot of data which I thereafter manipulate in excel, where I'm a lot more experienced.

This time though I'm trying to generate some Live Charts which have as input a single SQL query. I will now have to create complex tables without the aid of the excel tools I'm so familiar with.

The problem is the following:

We have telesales agents that book appointments by answering to inbound calls and making outbound cals. These will generate leads that might potentially result in a sale. The relevant tables and fields for this problem are these:

Contact Table
Agent

Sales Table
Price
OutboundCallDate

I want to know for each telesales agent their respective Total Sales amount in one column, and their outbound sales value in another.

The end result should look something like this:

+-------+------------+---------------+
| Agent | TotalSales | OutboundSales |
+-------+------------+---------------+
| Tom   |      30145 |             0 |
| Sally |      16449 |          1000 |
| John  |      10500 |           300 |
| Joe   |      50710 |             0 |
+-------+------------+---------------+

With the below SQL I get the following result:

SELECT contact.agent, SUM(sales.price)
FROM contact, sales
WHERE contact.id = sales.id
GROUP BY contact.agent

+-------+------------+
| Agent | TotalSales |
+-------+------------+
| Tom   |      30145 |
| Sally |      16449 |
| John  |      10500 |
| Joe   |      50710 |
+-------+------------+

I want to add the third column to this query result, in which the price is summed only for records where the OutboundCallDate field contains data. Something a bit like (where sales.OutboundCallDate is Not Null)

I hope this is clear enough. Let me know if that's not the case.

It's not entirely clear what you're asking. Do you want something like SUM(CASE WHERE sales.OutboundCallDate IS NOT NULL THEN sales.price ELSE 0 END)? — Dan
– Dan, Commented Aug 29, 2014 at 16:07
You made it look very easy! I Just had to correct Case Where for Case When. Thanks a million Dan! — Sergio
– Sergio, Commented Aug 30, 2014 at 5:47

Ram · Accepted Answer · 2014-08-29 16:39:04Z

2

Use CASE

SELECT c.Agent, 
       SUM(s.price) AS TotalSales,
       SUM(CASE 
               WHEN s.OutboundCallDate IS NOT NULL THEN s.price
               ELSE 0
           END) AS OutboundSales
FROM contact c, sales s
WHERE c.id = s.id
GROUP BY c.agent

edited Aug 29, 2014 at 16:39

answered Aug 29, 2014 at 16:30

Ram

3,09110 gold badges43 silver badges57 bronze badges

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

Comments

Isaac G Sivaa · Accepted Answer · 2014-08-29 16:42:32Z

0

I think the code would look

SELECT contact.agent, SUM(sales.price)
FROM contact, sales
WHERE contact.id = sales.id AND SUM(WHERE sales.OutboundCallDate)
GROUP BY contact.agent

edited Aug 29, 2014 at 16:42

Isaac G Sivaa

1,2994 gold badges15 silver badges32 bronze badges

answered Aug 29, 2014 at 16:17

Mario ibarra

1

Comments

Andy_in_Van · Accepted Answer · 2014-08-29 16:45:00Z

0

notI'm assuming your Sales table contains something like Units and Price. If it's just a sales amount, then replace the calculation with the sales amount field name.

The key thing here is that the value summed should only be the sales amount if the OutboundCallDate exists. If the OutboundCallDate is not NULL, then we're using a value of 0 for that row.

    select Agent.Agent, TotalSales = sum (sales.Price*Units)
        , OutboundSales =  sum (
                case when Outboundcalldate is not null then price*Units 
                    else 0 
                end)
    From Sales inner join Agent on Sales.Agent = Agent.Agent
    Group by Agent.Agent

answered Aug 29, 2014 at 16:45

Andy_in_Van

3312 silver badges5 bronze badges

Collectives™ on Stack Overflow

Adding another column based on different criteria (SQL-server)

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related