Display query result differently SQL

Question

Display query result differently SQL

Navigation

#1 by (0 votes)
#2 by (0 votes)

2

I have the following query:

SELECT pr_products.product AS PRODUCT, pr_varieties.variety AS VARIETY, pr_grades.GRADE, SUM(pf_harvest.quantity) AS QUANTITY
FROM pf_harvest
INNER JOIN pf_performance ON pf_performance.id = pf_harvest.id_performance
INNER JOIN pr_products ON pr_products.id = pf_harvest.id_product
INNER JOIN pr_varieties ON pr_varieties.id = pf_harvest.id_variety
INNER JOIN pr_grades ON pr_grades.id = pf_harvest.id_grade
WHERE pf_performance.status = 100
AND pf_harvest.id_tenant = 1
AND pf_harvest.date = '2017-03-22'
GROUP BY pf_harvest.id_product, pf_harvest.id_variety, pf_harvest.id_grade
ORDER BY pf_harvest.id_product, pr_varieties.variety, pf_harvest.id_grade;

that shows me the following result:

-------------------------------------------------------------------
   PRODUCT     |     VARIETY     |     GRADE      |     QUANTITY  |
-------------------------------------------------------------------
   ROSE        |     ROSEV1      |     GRADE1     |     1000      |
-------------------------------------------------------------------
   ROSE        |     ROSEV1      |     GRADE2     |     5000      |
-------------------------------------------------------------------
   ROSE        |     ROSEV2      |     GRADE1     |     2000      |
-------------------------------------------------------------------
   ROSE1       |     ROSE1V1     |     GRADE1     |     3500      |
-------------------------------------------------------------------

Is it possible to show the result of the query in the following way?

-------------------------------------------------------------------
  PRODUCT     |   VARIETY   |  GRADE1  |  GRADE2 |  TOTAL         |  
-------------------------------------------------------------------
   ROSE       |   ROSEV1    |  1000    |  5000   |  6000          |
-------------------------------------------------------------------
   ROSE       |   ROSEV2    |  2000    |    0    |  2000          |
-------------------------------------------------------------------
   ROSE1      |   ROSE1V1   |  3500    |    0    |  3500          |
-------------------------------------------------------------------

I tried to change the query but I could not and I would like to know if it is possible, I hope someone can help me.

thanks

mysql sql

asked by Fabian Sierra 29.03.2017 в 22:17

source

2 answers

0

You are showing GRADE as a descriptive field and QUANTITY as the value, what you must do is show the value of GRADE1 and GRADE2 and then add them, you will have to reuse the table pf_harvest and rename it pf_harvest AS GRADE1 & pf_harvest AS GRADE2

answered by 29.03.2017 в 23:02

What is the difference between using the entity data model and the POCO template? Subtract two fields from a table in PHP mysql?

score 0 · Accepted Answer

To obtain the result as you describe it, you have to display the quantities in two conditional sums:

SELECT pr_products.product AS PRODUCT, 
       pr_varieties.variety AS VARIETY, 
       pr_grades.GRADE, 
       SUM(IF(pr_grades.GRADE='GRADE1',pf_harvest.quantity,0)) as GRADE1,
       SUM(IF(pr_grades.GRADE='GRADE2',pf_harvest.quantity,0)) as GRADE2,
       SUM(pf_harvest.quantity) AS TOTAL
FROM pf_harvest
JOIN ...
WHERE ...
GROUP BY ...
ORDER BY ...

I have two additional comments:

You were not grouping by pr_grades.GRADE. If the query worked, then for this modification you should remove pf_harvest.id_grade from GROUP BY

You will have to make as many conditional sums as different GRADES you have in your table pr_grades .