SQL Query using 5 aggregate functions

Question

How do I query the following:

For each customer, product and month, count the number of sales transactions that were between the previous and the following month's average sales quantities. For January and December, display NULL or 0. Can only use: 5 aggregate functions (sum, count, avg, max & min)

This is the table reference:

create table sales
    (
        cust    varchar(20),
        prod    varchar(20),
        day     integer,
        month   integer,
        year    integer,
        state   char(2),
        quant   integer,
        date    date
    );

Schema:

Example of my Desired Result

I am stuck with the following codes. I'm having a hard time how to execute it.

SELECT cust, prod, month, COUNT(*) AS SALES_COUNT_BETWEEN_AVGS
FROM sales

I use MySQL. Please guide me thank you.

Hello, first share with us what database do you use ? Oracle ? MySQL ? SQLServer ?... Then show us some example data and expected result from that data... Then show us what have you tried so far ? — VBoka
– VBoka, Commented Apr 20, 2022 at 6:50
Hello @mariakz please edit your question and add that tag... The same way you added SQL tag — VBoka
– VBoka, Commented Apr 20, 2022 at 6:53
@VBoka I added it already, sorry. Also I'm having a hard time how to query the following.. I am stuck in SELECT cust, prod, month, COUNT(*) AS SALES_COUNT_BETWEEN_AVGS FROM sales... idk what to put next — mariakz
– mariakz, Commented Apr 20, 2022 at 6:56

DhruvJoshi · Accepted Answer · 2022-04-20 07:48:55Z

1

Maybe try a query like below

the first part is to calculate averages using group by
second part is to use to those averages in a JOIN twice for past month and future month
third part is WHERE clause in which we compare data. Note we have used greatest and least functions to determine min and max between two values from past and next month

Query

WITH T AS 
(SELECT cust, prod, month, AVG(quant) AS avg_quantity
FROM sales
group by cust, prod, month
)


SELECT S.cust, S.prod, S.month, COUNT(1) AS Sales_count
FROM sales S
LEFT JOIN T T1
ON T1.cust=S.Cust AND 
T1.prod=S.Prod AND
T1.Month=S.Month-1
LEFT JOIN T T2
ON T2.cust=S.Cust AND 
T2.prod=S.Prod AND
T2.Month=S.Month+1
WHERE S.quant BETWEEN IFNULL(LEAST(T1.avg_quantity,T2.avg_quantity),0) AND IFNULL(GREATEST(T1.avg_quantity,T2.avg_quantity),0)

edited Apr 20, 2022 at 7:48

answered Apr 20, 2022 at 7:22

DhruvJoshi

17.2k6 gold badges46 silver badges63 bronze badges

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

3 Comments

mariakz Over a year ago

It gives me an error saying that column 'cust'in field list is ambiguous.

mariakz Over a year ago

Also, should I execute the above codes all together? I mean they are not separated right.

DhruvJoshi Over a year ago

@mariakz yes execute together. and Ive corrected the ambiguity in query

Collectives™ on Stack Overflow

SQL Query using 5 aggregate functions

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related