Jump to content

Edit History

reinforcedItler

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

reinforcedItler

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

reinforcedItler

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

×
×
  • Create New...