Adding column data based on column values?

Question

I have a query result like this https://i.sstatic.net/sDuIj.png

EDIT: Here is the actual table http://pastebin.com/TZCGHKdt

SECOND EDIT: http://sqlfiddle.com/#!2/49bae/1

If u see the result in the SQLFIDDLE link, it shows duplicate entries in ID column. For example the value 26 in the ID Column has a total of 4 values, the query shows them broken up into 3 and 1. I want them joined.

Here is the insert query for the table that i'm using

    INSERT INTO `capture_captive` (`capture_id_1`, `capture_id_2`, `capture_id_3`,      `capture_id_4`, `capture_id_5`)
 VALUES
(23, 32, 0, 0, 0),
(26, 25, 24, 0, 15),
(26, 32, 0, 0, 0),
(0, 0, 0, 0, 0),
(26, 26, 0, 0, 0),
(32, 32, 0, 0, 0);

The query that i'm using is

    select id, num from 
    (select `capture_id_1` id, (COUNT(capture_id_1)) num from capture_captive where capture_id_1<>0 group by capture_id_1 
    UNION
    select `capture_id_2`, (COUNT(capture_id_2)) num from capture_captive where capture_id_2<>0 group by capture_id_2
    UNION
    select `capture_id_3`, (COUNT(capture_id_3)) num from capture_captive where capture_id_3<>0 group by capture_id_3
    UNION
    select `capture_id_4`, (COUNT(capture_id_4)) num from capture_captive where capture_id_4<>0 group by capture_id_4 
    UNION
    select `capture_id_5`, (COUNT(capture_id_5)) num from capture_captive where capture_id_5<>0 group by capture_id_5 ) as E
    where id<>0
    order by id;

I want to show the total number of id, against their ids.

Thanks in advance.

Please don't use image files to describe your data/tables. Either provide the relevant data as SQL (as you did) -- in addition, you could provide a sqlfiddle.com corresponding to your issue. — Sylvain Leroux
– Sylvain Leroux, Commented Aug 20, 2013 at 8:30

Sylvain Leroux · Accepted Answer · 2013-08-20 09:02:05Z

1

Some might say this should be a comment, but...

Your main problem here is not with the query itself. You have a normalization problem. And that problem leads to sub-optimal queries as you discovered yourself.

You should definitively think about re-factoring your data table. Here is a possible "equivalent" (see http://sqlfiddle.com/#!2/759b9/2):

CREATE TABLE `capture_captive_norm` (`capture_group` int not null,
                                `capture_id` int not null,
                                `value` int,
                                    PRIMARY KEY (`capture_group`, `capture_id`));

INSERT INTO `capture_captive_norm` (`capture_group`, `capture_id`, `value`)
 VALUES
(1,1,23), (1,2,32), (1,3,0), (1,4,0), (1,5,0),
(2,1,26), (2,2,25), (2,3,24), (2,4,0), (2,5,15),
(3,1,26), (3,2,32), (3,3,0), (3,4,0), (3,5,0),
(4,1,0), (4,2,0), (4,3,0), (4,4,0), (4,5,0),
(5,1,26), (5,2,26), (5,3,0), (5,4,0), (5,5,0),
(6,1,32), (6,2,32), (6,3,0), (6,4,0), (6,5,0);

I agree this looks more complicated. But:

I want to show the total number of id, against their ids.

This in now simply:

SELECT `capture_id`, COUNT(IF(`value` <> 0, 1, NULL))
FROM `capture_captive_norm`
GROUP BY `capture_id`;

Producing:

+-------------+----------------------------------+
| CAPTURE_ID  | COUNT(IF(`VALUE` <> 0, 1, NULL)) |
+-------------+----------------------------------+
|          1  |                                5 |
|          2  |                                5 |
|          3  |                                1 |
|          4  |                                0 |
|          5  |                                1 |
+-------------+----------------------------------+

As you understand, based on that example, you might now easily query that table to count captures by value or value by id.

Please note that, by using the special NULL value to represent non-existent data, this might have been shortened to:

SELECT `capture_id`, COUNT(`value`)
FROM `capture_captive_norm`
GROUP BY `capture_id`;

Well ... in fact, with this sheme, you don't have to insert NULL for missing values. If you just "don't insert them", of course COUNT() will not count them...

edited Aug 20, 2013 at 9:02

answered Aug 20, 2013 at 8:34

Sylvain Leroux

52.3k8 gold badges114 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Ahsan Zaheer Over a year ago

How do you suggest I do that?

Ahsan Zaheer Over a year ago

Actually the Insert i showed is just half the table. Let me edit the Question with actual table

Sylvain Leroux Over a year ago

@ahsanzaheer Re-factoring is even more important now, I think. Considering your needs, you probably need to model your data using two tables and using a one-to-many relationship between them ("one capture group" is related to "many capture data"). As this is a higher level topic, may I suggest you to do some researches about that first, and then, if needed, to ask an other question requesting clarifications.

Ahsan Zaheer Over a year ago

I'm determined to solve this using the same table structure :D

Ahsan Zaheer Over a year ago

Sylvain, Please check my answer ( though it says i have to wait 2 days before i can mark it as an answer :P ). This is what i was looking for.

Ahsan Zaheer · Accepted Answer · 2013-08-20 11:33:51Z

1

Just found the answer to my own question. I was missing a very basic function SUM(). Probably needed a break.

Here is the link to it. http://sqlfiddle.com/#!2/49bae/2

Hope it helps

answered Aug 20, 2013 at 11:33

Ahsan Zaheer

6762 gold badges12 silver badges20 bronze badges

2 Comments

Sylvain Leroux Over a year ago

SUM() is clearly an option here. I don't have time to check that in details, but as you proposed, it required one temporary table+filesort for every sub-select. Depending your needs, the extra load on the DB might or might not be a problem. I posted an alternative answer probably more "server-friendly".

Ahsan Zaheer Over a year ago

Yes, I won't have server loading issues.

Sylvain Leroux · Accepted Answer · 2013-08-20 12:17:46Z

1

If you want to keep your original table structure, the following query should produce the expected result, requiring a lot less temporary tables and filesort than the one your proposed. See http://sqlfiddle.com/#!2/49bae/4 to compare both of them (click on "View Execution Plan" to view the query execution plan):

SELECT `id`, COUNT(*)
FROM (
  SELECT `capture_id_1` as `id` FROM `capture_captive`
  UNION ALL SELECT `capture_id_2` FROM `capture_captive`
  UNION ALL SELECT `capture_id_3` FROM `capture_captive`
  UNION ALL SELECT `capture_id_4` FROM `capture_captive`
  UNION ALL SELECT `capture_id_5` FROM `capture_captive`
  ) AS S
WHERE `id` <> 0
GROUP BY `id`
ORDER BY `id`

answered Aug 20, 2013 at 12:17

Sylvain Leroux

52.3k8 gold badges114 silver badges136 bronze badges

Collectives™ on Stack Overflow

Adding column data based on column values?

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related