sqlite count in to column

Question

I got 2 queries, 2 counts. But when I write my statement count(), count(), I'm getting the same count un both columns. Can you help me out please?

I already tried different ways (one example at the end)

These are my two separate queries

SELECT 
    x.ROWa, x.ROWb, COUNT(*) ResultA 
FROM
    tableA x
LEFT JOIN 
    tableB AS y ON SUBSTR(x.ROWb, 4, 1) = y.ROWz
WHERE 
    x.ROWc IN (5, 6) 
    AND (SUBSTR(x.ROWb, 4, 1) = "6" OR SUBSTR(x.ROWb, 4, 1) = "9") 
    AND Workflow = "VALUEone" 
    AND Shorthistory = "False"
GROUP BY 
    1
ORDER BY
    3 DESC, 2, 1 ASC

SELECT 
    x.ROWa, x.ROWb, COUNT(*) ResultB 
FROM
    tableA x
LEFT JOIN 
    tableB AS y ON SUBSTR(x.ROWb, 4, 1) = y.ROWz
WHERE 
    x.ROWc IN (5, 6) 
    AND (SUBSTR(x.ROWb, 4, 1) = "6" OR SUBSTR(x.ROWb, 4, 1) = "9") 
    AND Status = "successfull"
GROUP BY
    1
ORDER BY
    3 DESC, 2, 1 ASC

What I get is this in short:

ROWa|ROWb|ResultA|ResultB
-------------------------
aaa |123 |78    |78
bbb |456 |30    |30

But it should give the the actual result:

ROWa|ROWb|ResultA|ResultB
-------------------------
aaa |123 |78    |250
bbb |456 |30    |74

What I tried:

SELECT 
    x.ROWa, x.ROWb, 
    COUNT((SELECT x.ROWb 
           FROM tableA x
           LEFT JOIN tableB AS y ON SUBSTR(x.ROWb, 4, 1) = y.ROWz
           WHERE x.ROWc IN (5, 6) 
             AND (SUBSTR(x.ROWb, 4, 1) = "6" OR SUBSTR(x.ROWb, 4, 1) = "9") 
             AND Workflow = "VALUEone" 
             AND Shorthistory = "False"
           GROUP BY 1)) ResultA,
    COUNT((SELECT x.ROWb 
           FROM tableA x
           LEFT JOIN tableB AS y ON SUBSTR(x.ROWb, 4, 1) = y.ROWz
           WHERE x.ROWc IN (5, 6) 
             AND (SUBSTR(x.ROWb, 4, 1) = "6" OR SUBSTR(x.ROWb, 4, 1) = "9") 
             AND Status = "successfull"
           GROUP BY 1)) ResultB
GROUP BY
    1
ORDER BY
    3 DESC, 2, 1 ASC

user_10498828 · Answer

You can combine the 2 queries into 1 if you use conditional aggregation:

SELECT x.ROWa, x.ROWb, 
       COUNT(CASE WHEN Workflow = 'VALUEone' AND Shorthistory = 'False' THEN 1 END) ResultA,
       COUNT(CASE WHEN Status = 'successfull' THEN 1 END) ResultB  
FROM tableA x LEFT JOIN tableB y 
ON SUBSTR(x.ROWb, 4, 1) = y.ROWz
WHERE x.ROWc in (5,6) AND SUBSTR(x.ROWb,4,1) IN ('6', '9') 
GROUP BY x.ROWa

Or:

SELECT x.ROWa, x.ROWb, 
       SUM(Workflow = 'VALUEone' AND Shorthistory = 'False') ResultA,
       SUM(Status = 'successfull') ResultB  
FROM tableA x LEFT JOIN tableB y 
ON SUBSTR(x.ROWb, 4, 1) = y.ROWz
WHERE x.ROWc in (5,6) AND SUBSTR(x.ROWb,4,1) IN ('6', '9')  
GROUP BY x.ROWa

sqlite count in to column

1 Answers