CODEWITHSUNDEEP
sqlsql-server
Sergio
Sergio

Reputation: 503

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

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.

Upvotes: 2

Views: 3901

Answers (3)

Andy_in_Van
Andy_in_Van

Reputation: 331

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

Upvotes: 0

Mario ibarra
Mario ibarra

Reputation: 1

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

Upvotes: 0

Ram
Ram

Reputation: 3091

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

Upvotes: 2

Related Questions