nicoco - 6 months ago 35

MySQL Question

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

- a that can be
`state`

,`sold`

,`waiting`

or`unknown`

`cancelled`

- a
`vendor`

- a
`buyer`

- a that is either the
`cancellor`

or the`vendor`

in case the sale's`buyer`

is`state`

, and`cancelled`

for other states.`null`

I want to compute the cancelling rate for

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

The second one gets the number of sales where

`state`

`unknown`

`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 diving 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`

Answer

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.

Source (Stackoverflow)