MySql Query get amount of ordered products in a timespan

[reinforcedItler]

Hey Y'all,

maybe one of you can help me. I am trying to write this SQL-Query for PrestaShop, where ultimately you get the number of ordered products for specific products in an adjustable timeline (like from yesterday to today), and then you can download it as .csv.

I got it to work, that:

1. The two tables are compared
2. It looks for specific products

What doesn't work :

1. Count the ordered products
2. adjust the timespan

I tried everything, with variables(local, user defined), but nothing worked.
I then came across the COUNT Function, but it won't work either.

MySQL just feels not right for me, but here I am, as an Intern

SELECT
  j.id_order,
  j.date_add,
  o.id_order,
  o.product_name,
  o.product_id,
  IF(
    o.product_id = 28,
    (COUNT(o.product_id = 28)),
    IF(
      o.product_id = 30,
      (COUNT(o.product_id = 30)),
      IF(
        o.product_id = 31,
        (COUNT(o.product_id = 31)),
        IF(
          o.product_id = 32,
          (COUNT(o.product_id = 32)),
          IF(
            o.product_id = 33,
            (COUNT(o.product_id = 33)),
            IF(
              o.product_id = 34,
              (COUNT(o.product_id = 34)),
              IF(
                o.product_id = 35,
                (COUNT(o.product_id = 35)),
                IF(
                  o.product_id = 36,
                  (COUNT(o.product_id = 36)),
                  IF(
                    o.product_id = 37,
                    (COUNT(o.product_id = 37)),
                    IF(
                      o.product_id = 38,
                      (COUNT(o.product_id = 38)),
                      IF(
                        o.product_id = 39,
                        (COUNT(o.product_id = 39)),
                        IF(
                          o.product_id = 40,
                          (COUNT(o.product_id = 40)),
                          IF(
                            o.product_id = 41,
                            (COUNT(o.product_id = 41)),
                            IF(
                              o.product_id = 42,
                              (COUNT(o.product_id = 42)),
                              IF(
                                o.product_id = 43,
                                (COUNT(o.product_id = 43)),
                                IF(
                                  o.product_id = 44,
                                  (COUNT(o.product_id = 44)),
                                  IF(
                                    o.product_id = 45,
                                    (COUNT(o.product_id = 45)),
                                    IF(
                                      o.product_id = 106,
                                      (COUNT(o.product_id = 106)),
                                      IF(
                                        o.product_id = 107,
                                        (COUNT(o.product_id = 107)),
                                        IF(
                                          o.product_id = 108,
                                          (COUNT(o.product_id = 108)),
                                          IF(
                                            o.product_id = 109,
                                            (COUNT(o.product_id = 109)),
                                            IF(
                                              o.product_id = 110,
                                              (COUNT(o.product_id = 110)),
                                              IF(
                                                o.product_id = 111,
                                                (COUNT(o.product_id = 111)),
                                                555
                                              )
                                            )
                                          )
                                        )
                                      )
                                    )
                                  )
                                )
                              )
                            )
                          )
                        )
                      )
                    )
                  )
                )
              )
            )
          )
        )
      )
    )
  )
FROM
  ps1742_order_detail o,
  ps1742_orders j
WHERE
  o.id_order = j.id_order

I am using https://www.eversql.com/sql-syntax-check-validator/ to help me with the Coding.

Regards HeadReinforcedItler

Edited September 14, 2022 by reinforcedItler (see edit history)

[ps8modules]

Hi.

add where:

AND (j.date_add BETWEEN '2022-01-01' AND '2022-03-15')