I need the sum of each year separately for a specific product and a specific year-CodePudding

SELECT  TRX_DATE, SUM( UNIT_SELLING_PRICE) 
FROM ra_customer_trx_lines_all , ra_customer_trx_all
WHERE 
DESCRIPTION LIKE '%مبيعات نيتروجين سائل%'
GROUP BY  DESCRIPTION ,TRX_DATE

the failed result

CodePudding user response：

If you need a year, then extract it from the date column:

  SELECT EXTRACT (YEAR FROM trx_date) year, 
         SUM (unit_selling_price) total
    FROM ra_customer_trx_lines_all, ra_customer_trx_all
   WHERE description LIKE '%مبيعات نيتروجين سائل%'
GROUP BY description, EXTRACT (YEAR FROM trx_date)

You additionally put description into the group by clause, but it's not part of the select column list. It doesn't have to be, but - I'say that you'd actually want it to be:

  SELECT EXTRACT (YEAR FROM trx_date) year, 
         description,                           --> this
         SUM (unit_selling_price) total

Note that you're creating a Cartesian product (two tables which aren't joined by anything), so you'll cross join all rows from ra_customer_trx_lines_all with all rows from ra_customer_trx_all (and then filter by description).

Finally, you should always use table aliases when referencing columns. The way you wrote it, it is unknown which column belongs to which table. You might even get an error if the same column exists in both tables (it'll become ambiguous).

CodePudding user response：

use this

SELECT  EXTRACT (YEAR FROM trx_date), SUM( UNIT_SELLING_PRICE) 
FROM ra_customer_trx_lines_all , ra_customer_trx_all
WHERE 
DESCRIPTION LIKE '%مبيعات نيتروجين سائل%'
GROUP BY  DESCRIPTION ,EXTRACT (YEAR FROM trx_date)

CodePudding user response：

are you missing join statement?

SELECT  EXTRACT (YEAR FROM trx_date), SUM(UNIT_SELLING_PRICE) 
FROM ra_customer_trx_lines_all a 
join ra_customer_trx_all b on a.some_id = b.some_id
WHERE DESCRIPTION LIKE '%مبيعات نيتروجين سائل%'
GROUP BY EXTRACT (YEAR FROM trx_date)

as mentioned previously you may also use specific alias for each column:

SELECT  EXTRACT (YEAR FROM a.trx_date), SUM(b.UNIT_SELLING_PRICE) 
FROM ra_customer_trx_lines_all a 
join ra_customer_trx_all b on a.trx_id = b.trx_id
WHERE a.DESCRIPTION LIKE '%مبيعات نيتروجين سائل%'
GROUP BY EXTRACT (YEAR FROM a.trx_date)