SELECT total sales, total active customers, avg. spend per customer, avg. spend per transaction for 2018 and 2019, respectively?

Syed Ariff 0
Viewed 34

I have this block of data

https://www.db-fiddle.com/f/vXApR1qr7nQhcg5t2Tuxkx/1

enter image description here

So I would like to obtain the total sales,total active customers ,average spend per customer and avg spend per transaction in ONE SQL query.

Is this possible to do?

Here is the query that I have done but the subquery is not working for me. Please help.

select year(transdate) as year,truncate(sum(price * quantity),2) AS totalsales,COUNT(DISTINCT(b.custid)) as activecustomers,

SELECT truncate(AVG(total),2) AS AVERAGE 
FROM (SELECT custid, sum(price*quantity) AS total 
      FROM transaction GROUP BY custid) A
    ;

from transaction 
where transdate >= '2018-01-01' and transdate <= '2019-12-31'
group by YEAR(transdate)
mysql sql
1 Answers

Answering this question -> the total sales, total active customers, average spend per customer and avg spend per transaction in ONE SQL query.

select sum(price*quantity) as total_sales, count(distinct custid) as 'active user', sum(price*quantity)/count(distinct custid) as 'avg spend per customer', sum(price*quantity)/count(transid) as 'avg spend per transition' from transaction

Dharm paaji0
Related