Photoshop Question posted in
The official user guide can be found here.

SQL make 2 different date types in one query – Photoshop

I made a query that has 3 tables: 1 with current year, last year and name of the country
enter image description here

As you can see the results of current year and last year are the same and that is the problem here. I want to use like. I want to make 1 query with one that has current year and one with last year.

I can use this for last year YEAR(o.date_add) = YEAR(NOW() - INTERVAL 1 YEAR)

And from current year i can use this for example: YEAR(o.date_add) = YEAR(CURRENT_DATE())

My query:

SELECT
    IFNULL(SUM(DISTINCT o.total_paid_tax_excl), 0)  AS CurrentYEAR,
    IFNULL(SUM(DISTINCT o.total_paid_tax_excl), 0)  AS LastYEAR,
    IFNULL(CONCAT(l.name), 0) AS name
FROM
    expoled.ps_orders o
        LEFT JOIN
    expoled.ps_address a ON o.id_address_delivery = a.id_address
        INNER JOIN
    expoled.ps_country c ON a.id_country = c.id_country
        INNER JOIN
    expoled.ps_country_lang l ON c.id_country = l.id_country
WHERE
    l.id_lang = 7
        AND o.current_state IN (3 , 4, 5, 9, 13, 15, 20, 22, 23, 24, 25, 121)
        AND l.name NOT IN ('Netherlands')
GROUP BY (l.name)

I can make seperate query but i want it in 1 query how can i archief this?
I did try Union but the results stayed the same.

I want to have it like this(this one does not work!! just for example[photoshop] ddd

I think I need to change this part of the query

SELECT
    IFNULL(SUM(o.total_paid_tax_excl), 0)  AS CurrentYEAR,
    IFNULL(SUM(o.total_paid_tax_excl), 0)  AS LastYEAR,
    IFNULL(CONCAT(l.name), 0) AS name
FROM

And add this to my query

For CurrentYEAR = YEAR(o.date_add) = YEAR(CURRENT_DATE())

For LastYEAR = YEAR(o.date_add) = YEAR(NOW() - INTERVAL 1 YEAR)

Tags:

2

Answers


  1. 0

    You can use a conditional expression (if() function or case statement) within the sum() aggregate function to add only those values that fit the criteria:

    SELECT
    IFNULL(SUM(DISTINCT if(YEAR(o.date_add) = YEAR(CURRENT_DATE()),o.total_paid_tax_excl, 0)), 0)  AS CurrentYEAR,
    IFNULL(SUM(DISTINCT if(YEAR(o.date_add) = YEAR(CURRENT_DATE())-1,o.total_paid_tax_excl, 0)), 0)  AS LastYEAR,
    IFNULL(l.name, '') AS name
FROM
    expoled.ps_orders o
        LEFT JOIN
    expoled.ps_address a ON o.id_address_delivery = a.id_address
        INNER JOIN
    expoled.ps_country c ON a.id_country = c.id_country
        INNER JOIN
    expoled.ps_country_lang l ON c.id_country = l.id_country
WHERE
    l.id_lang = 7
        AND o.current_state IN (3 , 4, 5, 9, 13, 15, 20, 22, 23, 24, 25, 121)
        AND l.name NOT IN ('Netherlands')
GROUP BY IFNULL(l.name, '')

    I also simplified the expression for the name field and made the group by clause consistent with it.

    I did not change any other parts of the query, although I do not really agree with the distinct within the sum(), since you could have the same value paid as tax, but it is your data.

    If your table has older data than the previous year’s, then consider adding a where criteria to restrict the records for which the calculations are run.

    Login or Signup to reply.
  2. 0

    I think you can try something like this (I added CASE for fields with SUM and a Where condition to extract only relevant years):

    SELECT
    IFNULL(SUM(  CASE WHEN YEAR(CURRENT_DATE)-YEAR(o.date_date) =0 THEN o.total_paid_tax_excl ELSE 0 END), 0)  AS CurrentYEAR,
    IFNULL(SUM( CASE WHEN YEAR(CURRENT_DATE)-YEAR(o.date_date) =1 THEN o.total_paid_tax_excl ELSE 0 END), 0)  AS LastYEAR,
    IFNULL(CONCAT(l.name), 0) AS name
FROM
    expoled.ps_orders o
        LEFT JOIN
    expoled.ps_address a ON o.id_address_delivery = a.id_address
        INNER JOIN
    expoled.ps_country c ON a.id_country = c.id_country
        INNER JOIN
    expoled.ps_country_lang l ON c.id_country = l.id_country
WHERE
    l.id_lang = 7
        AND o.current_state IN (3 , 4, 5, 9, 13, 15, 20, 22, 23, 24, 25, 121)
        AND l.name NOT IN ('Netherlands')
        AND YEAR(o.date_date) IN (YEAR(CURRENT_DATE()), YEAR(CURRENT_DATE)-1)
GROUP BY (l.name)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search