SQL query to add Rows and display in two Columns in MYSQL

Question

SQL query to add Rows and display in two Columns in MYSQL

Asked 5 years, 2 months ago

Viewed 152 times

0

I would like to display the sum of several rows, only in two columns. All information is in the same table. Only the criteria change.
Ex: I want the SUM of a column with WHERE and I want the SUM of the same column only with another WHERE. And these values are shown in two columns (SUM1 and SUM2).

I’m wearing it like this, but it comes in lines:

SELECT 
     SUM(ITEMSAIDA.NVLRUNITARIO), 
     ITEMSAIDA.NCODLINHAMERC, 
     ITEMSAIDA.NCODVENDEDOR 
  FROM ITEMSAIDA 
  WHERE 
     ITEMSAIDA.CNPJ = 'XX.XXX.XXX/0001-XX' AND 
     VENDEDOR.CNPJ =  'XX.XXX.XXX/0001-XX' AND 
     (
        ITEMSAIDA.NCODLINHAMERC = 13 OR 
        ITEMSAIDA.NCODLINHAMERC = 24
     ) 
  GROUP BY ITEMSAIDA.NCODVENDEDOR, ITEMSAIDA.NCODLINHAMERC

Thank you

you can use the case when.. but it is not very clear the condition of each one. details the question better

– rLinhares

2019/09/04 at 14:04

1 answer

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.

by anonimo • **4,084** points · Answer 1 · 2019-09-04T14:18:29+00:00

Assuming that SUM1 refers to ITEMSAIDA.NCODLINHAMERC = 13 and SUM2 to ITEMSAIDA.NCODLINHAMERC = 24, because you didn’t specify the criteria for each of the sums, you can try something like:

SELECT 
     CASE SUM(WHEN ITEMSAIDA.NCODLINHAMERC = 13 THEN ITEMSAIDA.NVLRUNITARIO ELSE 0) END AS SUM1, 
     CASE SUM(WHEN ITEMSAIDA.NCODLINHAMERC = 24 THEN ITEMSAIDA.NVLRUNITARIO ELSE 0) END AS SUM2, 
     ITEMSAIDA.NCODLINHAMERC, 
     ITEMSAIDA.NCODVENDEDOR 
  FROM ITEMSAIDA 
  WHERE 
     ITEMSAIDA.CNPJ = 'XX.XXX.XXX/0001-XX' AND 
     VENDEDOR.CNPJ =  'XX.XXX.XXX/0001-XX'
  GROUP BY ITEMSAIDA.NCODVENDEDOR, ITEMSAIDA.NCODLINHAMERC;