I have problem an now this is more than 2days and I can't find any answer. can you help me out guys. to solve my problem.

I use Server version: 10.4.6-MariaDB

Question: How to get the all records from the previous week by using the subquery ?

So now i have already have query getting the current record and previous week however the previous week getting wrong result the 4th column result from week today previous week..

This is my query:

SELECT market,storeid,CONCAT(FORMAT(sum(ns), 0)) as NS, 

(SELECT sum(ns) as nslw FROM cron_db.sales_report_export_1  WHERE txndate = CURDATE() - INTERVAL 7 DAY)

FROM cron_db.sales_report_export_1 as sales_report1
LEFT JOIN (SELECT store_code,market FROM cron_db.store_master) sm 
ON sales_report1.storeid = sm.store_code

WHERE market = "EDM" AND week = (SELECT DISTINCT(week) FROM cron_db.sales_report_export_1 WHERE txndate = SUBDATE(CURDATE(),2))
AND period = (SELECT DISTINCT(period) FROM cron_db.sales_report_export_1 WHERE txndate = SUBDATE(CURDATE(),2))
GROUP BY storeid

And the result:

result of my query

Expect output must be:

expected output

  • thank you i already update my post – SoulAiker Oct 17 '19 at 17:00
  • Specify server version. 5.x? 8.x? And explain where the last column values (4.343, 7.480, ...) are taken from? – Akina Oct 17 '19 at 17:09
  • @Akina thanks i already update – SoulAiker Oct 17 '19 at 17:32
  • Your MariaDB version supports window version of SUM function and RANGE BETWEEN frame specification. Use it. – Akina Oct 17 '19 at 18:02
  • @Akina yah i use the sum function on my sub query but the result is wrong. how to get the right answer? by grouping by storeid? – SoulAiker Oct 17 '19 at 18:10

CONCAT(FORMAT(sum(ns), 0)) --> COALESCE(FORMAT(sum(ns), 0))

txndate = CURDATE() - INTERVAL 7 DAY --> txndate >= CURDATE() - INTERVAL 7 DAY

What is SUBDATE(CURDATE(),2) supposed to do?

| improve this answer | |
  • SUBDATE(CURDATE(),2) i used this for getting the previous day – SoulAiker Oct 21 '19 at 8:15
  • to better understand, (the first net sales or NS ) for this condition = SUBDATE(CURDATE(),2) ------ the subquery = getting the previous week, so i have the column for Current Date Net Sales and Previous week net sales – SoulAiker Oct 21 '19 at 8:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.