SQL Query how to count different value in single row

Question

I am sure there must be a relatively straightforward way to do this, but it is escaping me at the moment. Suppose I have a SQL table like this:

+-----+-----+-----+-----+
|  A  |  B  |  C  |  D  |
+=====+=====+=====+=====+
|  a  |  b  |  3  | 100 |  << a,b
+-----+-----+-----+-----+
|  a  |  c  |  3  | 60  |  << a,c
+-----+-----+-----+-----+
|  a  |  b  |  4  | 50  |  << a,b
+-----+-----+-----+-----+
|  a  |  b  |  5  | 30  |  << a,b
+-----+-----+-----+-----+
|  d  |  b  |  3  | 35  |  << d,b
+-----+-----+-----+-----+
|  a  |  c  |  2  | 40  |  << a,c
+-----+-----+-----+-----+

Now, I want to know how many times each combination of values for columns A and B appear, then it can hold data column D based on grouping by column C in single row. So, in this example, I want an output something like this:

+-----+-----+-----+-----+-----+
|  A  |  B  |  C3 |  C4 |  C5 |
+=====+=====+=====+=====+=====+
|  a  |  b  | 100 | 50  | 30  | << a,b
+-----+-----+-----+-----+-----+
|  a  |  c  | 60  |  0  |  0  | << a,c
+-----+-----+-----+-----+-----+
|  d  |  b  | 35  |  0  |  0  | << d,b
+-----+-----+-----+-----+-----+

What would be the SQL to determine that? I feel like this must not be a very uncommon thing to want to do.

Thanks!

Where did the a, c ,2 ,40 go? And is there a primary key constraint on your table? — Chris Bednarski
– Chris Bednarski, Commented Feb 22, 2011 at 9:00

Borja · Accepted Answer · 2011-02-22 09:00:26Z

2

You can use:

SELECT A, B,
       sum(if(C=3, D, NULL)) as C3,
       sum(if(C=4, D, NULL)) as C4,
       sum(if(C=5, D, NULL)) as C5
  FROM yourTable
GROUP BY A, B;

answered Feb 22, 2011 at 9:00

Borja

2,2061 gold badge19 silver badges21 bronze badges

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

3 Comments

ypercubeᵀᴹ Over a year ago

This will only work if C column has values of 3, 4 or 5 only. So, the row with 2 (or other values besides those three) at C will dissapear.

Borja Over a year ago

Yes, you need to add the needed columns. On the question example are only C3, C4 and C5 because of this I only use this values.

Borja Over a year ago

Also, the rows without this C value will not disappear, only they will show 0 on C3, C4 and C5 columns

Don · Accepted Answer · 2011-02-22 08:59:59Z

0

You can achieve a similar result by using GROUP_CONCAT (see docs), which gives you a comma-separated list of values in column D and column C and then extract your data by program.

answered Feb 22, 2011 at 8:59

Don

17.7k13 gold badges67 silver badges106 bronze badges

Collectives™ on Stack Overflow

SQL Query how to count different value in single row

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related