SQLite - Complex Query

Question

This is what I want to get.

Art|CANTIDAD1|CANTIDAD2|CANTIDAD1CARGA1 |CANTIDAD2CARGA1 |CANTIDAD1CARGA2 | CANTIDAD2CARGA2
----------------------------------------------------------------------------------------------
001|    7    |    0    |       4        |        0       |        3       |        0
002|    0    |    2    |       0        |        1       |        0       |        1
003|    2    |    0    |       2        |        0       |        0       |        0
004|    3    |    0    |       1        |        0       |        2       |        0
005|    2    |    0    |       0        |        0       |        2       |        0
006|    0    |    1    |       0        |        0       |        0       |        1

I get CANTIDAD1 and CANTIDAD2 doing this query. It is the result of the sum of the amounts corresponding to the "where"

SELECT 
    SUM(D.NCANTIDAD1) AS NTOTCANTIDAD1, 
    SUM(D.NCANTIDAD2) AS NTOTCANTIDAD2 
FROM 
    CABPEDIDOS C, 
    DETPEDIDOS D, 
    ARTICULOS A 
WHERE 
    C.DFECHAALBARAN IS NULL 
    AND C.CSERIE = D.CSERIE 
    AND C.NPEDIDO = D.NPEDIDO 
    AND D.NFABRICANTE = A.NFABRICANTE 
    AND D.CARTICULO = A.CARTICULO 
GROUP BY 
    D.NFABRICANTE, D.CARTICULO, A.CNOMBRE

CANTIDAD1CARGA1, CANTIDAD2CARGA1 are quantities that are in the database (d.cantidad1, d,cantidad2 are the real names, I have to sum all of them to get CANTIDAD1 and CANTIDAD2), but I need to get the quantities corresponding to the respective C.CARGA:

(CANTIDAD1 = CANTIDAD1CARGA1 + CANTIDAD1CARGA2)

How can I get these values?

** C.NCARGA can have more than one value, I need to get all CANTIDAD1CARGA'x' and CANTIDAD2CARGA'x'

I don't care if I have to use two querys, 
- one for CANTIDAD1 and CANTIDAD2
- other for CANTIDAD1CARGA1, CANTIDAD2CARGA1, CANTIDAD1CARGA2... etc

It looks like what you want is a type of Pivot, which unfortunately doesn't seem to be supported in SqlLite. There are some [ideas here](http://stackoverflow.com/q/1237068/314291) — StuartLC, Jun 11 '14 at 17:05

score 0 · Answer 1 · answered Jun 11 '14 at 16:55

0

I have a feeling I'm not really understanding the question, but it seems like you just need:

SELECT CANTIDAD1CARGA1 + CANTIDAD1CARGA2 AS CANTIDAD1,
       CANTIDAD2CARGA1 + CANTIDAD2CARGA2 AS CANTIDAD2,
       CANTIDAD1CARGA1, CANTIDAD2CARGA1, CANTIDAD1CARGA2, CANTIDAD2CARGA2
FROM ...

answered Jun 11 '14 at 16:55

Barmar

741,623
53
500
612

I've edited the first post. I haven't got "CANTIDAD1CARGA1 or CANTIDAD2CARGA1", it's a bit hard to explain, I'm sorry. – Jose Antonio Benitez Montero Jun 11 '14 at 17:01
I don't understand. Please update your question to show all the input tables. – Barmar Jun 11 '14 at 17:05
1

Best would be to make a sqlfiddle with some sample data and your attempted solution. – Barmar Jun 11 '14 at 17:06

SQLite - Complex Query

1 Answers1