CODEWITHSUNDEEP
mysqlsqlmariadb
nicoco
nicoco

Reputation: 1563

Get the cancelling rate of a vendor ignoring a certain state, in one query

Let's say I have a table representing sales with these attributes:

I want to compute the cancelling rate for Joe. Right now I'm doing two SQL requests. The first one gets the number of cancelled sales by Joe:

SELECT COUNT(*) AS n_cancelled FROM sales WHERE cancellor = "Joe"

The second one gets the number of sales where Joe was involved, excluding the ones whose state is unknown or waiting:

SELECT COUNT(*) AS n_sales FROM sales
WHERE
  STATE in ("sold", "cancelled")
  AND (vendor = "Joe" OR buyer = "Joe")

I then compute my cancelling rate by dividing the results of these two requests.

I'm interested in doing so in one request that would output the cancelling rate and also the number of sales used to compute it (n_sales here). How would I do that?

Upvotes: 0

Views: 120

Answers (2)

Rick James
Rick James

Reputation: 142433

To do it all in one query:

SELECT 
    ( SELECT ... ) /   -- How many cancelled (as already formulated)
    ( SELECT ... );    -- Total number (as already formulated)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270573

You can use conditional aggregation:

SELECT SUM(cancellor = 'Joe') as n_cancelled,
       SUM(state in ('sold', 'cancelled') AND 'Joe' IN (vendor, buyer)) AS n_sales
FROM sales
WHERE ( (cancellor = 'Joe') or
        (state in ('sold', 'cancelled') AND 'Joe' IN (vendor, buyer))
      );

You can do the division in the query as well.

Note: This keeps the WHERE clause, which is not strictly necessary. But reducing the number of rows before aggregating the data can improve performance.

Upvotes: 1

Related Questions