Stuck - How to merge queries

Question

Hello experts,

I hope everyone's doing fine. I've had to use SQL CE compact for a small software here. I must say i've liked it so far. Light and works well.

My issue is, i need to run a single query which tells me the the result (count) in the form of a single number.

I am running the following query:

SELECT     COUNT(*) AS Numbatts
FROM         PowerQcList
WHERE     (fixed = 0) AND (port1Change = '1')
UNION
SELECT     COUNT(*) AS Numbatts
FROM         PowerQcList AS PowerQcList_1
WHERE     (fixed = 0) AND (port2Change = '2')
UNION
SELECT     COUNT(*) AS Numbatts
FROM         PowerQcList AS PowerQcList_1
WHERE     (fixed = 0) AND (port3Change = '3')
UNION
SELECT     COUNT(*) AS Numbatts
FROM         PowerQcList AS PowerQcList_1
WHERE     (fixed = 0) AND (port4Change = '4')

And i get the following output:

Numbatts 9

16

21

30

Which is what is expected. However, i want to do a query on this result so that it gives me just one number, which is the sum of all the numbers in the four rows output by the last query (9 + 16 + 21 +30). And most importantly, i want one query to do both the things. I have tried many SQL queries (after looking at different examples, etc) but didn't achieve the objective yet.

Please help!! :)

Thanks and Regards,
Saad

Answer 1

Hello Saad,

It depends a little bit on your data. In the easiest case you could combine the where clause conditions with an OR logic like:

SELECT     COUNT(*) AS Numbatts
 FROM         PowerQcList
 WHERE     (fixed = 0) 
       AND (port1Change = '1'
            OR port2Change = '2'
            OR port3Change = '3'
            OR port4Change = '4')

---

Olaf Helper

Blog Xing

Answer 2

Olaf,

Thanks for your response. The issue with what you have mentioned is that, it gives me a count of the rows where fixed=0 AND either of portXChange = X

What i'm really looking for is the number of instances where portXChange = X. One row can have one or two or three or four of these, and i don't want to count them as one number.

Let me clarify my data. It has the following columns, with some sample data

S.No fixed port1Change port2Change port3Change port4Change

123 0         1 3

434 0 1 2  4

122 0                              2 4

768 1          1 2 3

753 0                                                    3 4

532 0                                                    3 4

756  0           1                 2 3

534  0                              2 4

Now, if i run your SQL query, it will give me "7" as the result. What i want to get, is the sum of all instances where portXChange = X (and fixed=0 for the row). That would be "16" as the result.

I understand that i can accomplish the same by running 4 queries, one for each portXChange column, but i want to accomplish this in one query.

Looking forward to hearing from you experts.

Thanks and Regards,
Saad

Answer 3

I'm sorry but its destroying the formatting when i submit it.

The first number is the Serial number (3 digit). Then a value of 0 or 1 for "fixed" column. This is followed by 1 or 2 or 3 or 4, which appears below their respective columns. Like a 3 would appear under the column port3Change, and likewise.

 Please tell me if there's some other way to post some sample data here

Regards,
Saad

Answer 4

Hello Saad,

I already guessed something that way; that's why I mentioned "depends on your data". OK, other solution: Use your origin as sub query and sum the result. And important: Use UNION ALL, a UNION gives you a distinct result, means if you have 2 equal count result, UNION returns only one of it :

SELECT SUM(Numbatts) AS Total
FROM
    (SELECT     COUNT(*) AS Numbatts
     FROM         PowerQcList
     WHERE     (fixed = 0) AND (port1Change = '1')
     UNION ALL
     SELECT     COUNT(*) AS Numbatts
     FROM         PowerQcList AS PowerQcList_1
     WHERE     (fixed = 0) AND (port2Change = '2')
     UNION ALL
     SELECT     COUNT(*) AS Numbatts
     FROM         PowerQcList AS PowerQcList_1
     WHERE     (fixed = 0) AND (port3Change = '3')
     UNION ALL
     SELECT     COUNT(*) AS Numbatts
     FROM         PowerQcList AS PowerQcList_1
     WHERE     (fixed = 0) AND (port4Change = '4')
     ) AS DerivedTable

You can do a simple test: This query should return 20, but you get 10 as sum

SELECT SUM(Test) AS Test
FROM (SELECT 10 AS Test
      UNION
      SELECT 10 AS Test) AS DT

---

Olaf Helper

Blog Xing

Answer 5

Olaf,

The query worked like a charm. Thanks so so much for you help. I really appreciate it!

Have a nice day!!

Best Regards,
Saad