Implementation discussion

I am in a position where I want multiple counts from a single table based on different combination of conditions.

The table has 2 flags: A & B.

I want count for following criteria on same page:

1. A is true (Don't care about B)
2. A is false (Don't care about B)
3. A is true AND B is true
4. A is false AND B is true
5. A is true AND B is false
6. A is false AND B is false
7. B is true (Don't care about A)
8. B is false (Don't care about A)

I want all above count on same page. Which of following will a good approach for this:

1. Query for count on that table for each condition. [That is firing 8 queries every time user gives the command.]
2. Query for list of data fr开发者_JAVA技巧om database and then count values for appropriate conditions on UI.

Which option should I choose? Do you know any other alternative for this?

---

Your table essentially looks like this (The ID column is redundant, but I expect you have other data in your actual table anyway.):

CREATE TABLE `stuff` (
    `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
    `a` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    `b` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)

Some sample data:

INSERT INTO `stuff` (`id`, `a`, `b`) VALUES (1, 0, 0);
INSERT INTO `stuff` (`id`, `a`, `b`) VALUES (2, 0, 1);
INSERT INTO `stuff` (`id`, `a`, `b`) VALUES (3, 1, 0);
INSERT INTO `stuff` (`id`, `a`, `b`) VALUES (4, 1, 1);

This query (in mysql, I'm not sure about other DBMS) should produce the results you want.

select
count(if (a = 1,             1, NULL)) as one,
count(if (a = 0,             1, NULL)) as two,
count(if (a = 1 && b = 1,    1, NULL)) as three,
count(if (a = 0 && b = 1,    1, NULL)) as four,
count(if (a = 1 && b = 0,    1, NULL)) as five,
count(if (a = 0 && b = 0,    1, NULL)) as six,
count(if (b = 1,             1, NULL)) as seven,
count(if (b = 0,             1, NULL)) as eight
from stuff
group by null

With the sample, simple data above, the query generates:

one, two, three, four, five, six, seven, eight
2  , 2  , 1,     1,    1,    1,   2,     2

Notes:

group by null

This just causes every row ro be in the group.

count(...)

This function counts all the NON null values in the group, which is why we use the if(...) to return null if the condition is not met.

---

Create a query that already does the counting. At least with SQL this is not hard.

---

In my opinion 2nd option is better as you are querying only once. Firing 8 Queries to DB might later impact on performance.

---

Databases are designed to give you the data you want. In almost all cases, asking for what you want, is quicker than asking for everything and calculate or filter yourself. I'd say, you should blindly go for option 1 (ask what you need) and if it really does not work consider option 2 (or something else).

If every flag is true or false (no null values.) You don't need 8 queries, 4 would be enough.

1. Get the total
2. A true (don't care about B)
3. B true (don't care about A)
4. A and B true

'A true and B false' is second minus fourth, (A true) - (A and B true). And 'A and B false' = total - A true - B true + A and B true. Look for Inclusion exclusion principle for more information.