Finding maximum sales in a particular year

Question

I am working on a SQL query and need help with it. I'm using an Oracle database.

The DAILY_SALES table holds daily sales amount of each store and each customer; it has following 4 columns:

• STORE_NUMBER`
• CUSTOMER_NUMBER
• TRANSACTION_DATE
• SALES_AMOUNT

Will the following SQL query be able to find out all stores that had more than 20000 dollars in sales in the year of 2013?

Select * from DAILY_SALES where SALES_AMOUNT>20000 and TRANSACTION_DATE is in between (Jan1,2013 and Dec31,2013)

Answer 1

Select STORE_NUMBER, TRANSACTION_DATE, SALES_AMOUNT
from DAILY_SALES 
where transaction_date >= to_date('2013-01-01', 'YYYY-MM-DD')
  and transaction_date < to_date('2014-01-01', 'YYYY-MM-DD')
Group by STORE_NUMBER, TRANSACTION_DATE
having sum(sales_amount) > 20000;

You want to display only these three columns with these restriction:

• the year of the transaction is in 2013 (from 1/1/2013 right until the last day of 2013). You group the results by the STORE_NUMBER and the transaction date.

I think the code above will work for you.

Answer 2

The short answer to your question: No. Your query will not work.

Assuming your transaction_date column is of type Date, then this query should do what you want:

select store_number, sum(sales_amount)
from daily_sales
where transaction_date >= to_date('2013-01-01', 'YYYY-MM-DD')
and transaction_date < to_date('2014-01-01', 'YYYY-MM-DD')
group by store_number
having sum(sales_amount) > 20000