Reputation: 421
I've got a table TABLE1
like this:
|--------------|--------------|--------------|
| POS | TYPE | VOLUME |
|--------------|--------------|--------------|
| 1 | A | 34 |
| 2 | A | 2 |
| 1 | A | 12 |
| 3 | B | 200 |
| 4 | C | 1 |
|--------------|--------------|--------------|
I want to get something like this (TABLE2
):
|--------------|--------------|--------------|--------------|--------------|
| POS | Amount_A | Amount_B | Amount_C | Sum_Volume |
|--------------|--------------|--------------|--------------|--------------|
| 1 | 2 | 0 | 0 | 46 |
| 2 | 1 | 0 | 0 | 2 |
| 3 | 0 | 1 | 0 | 200 |
| 4 | 0 | 0 | 1 | 1 |
|--------------|--------------|--------------|--------------|--------------|
My Code so far is:
SELECT
(SELECT COUNT(TYPE)
FROM TABLE1
WHERE TYPE = 'A') AS [Amount_A]
,(SELECT COUNT(TYPE)
FROM TABLE1
WHERE TYPE = 'B') AS [Amount_B]
,(SELECT COUNT(TYPE)
FROM TABLE1
WHERE TYPE = 'C') AS [Amount_C]
,(SELECT SUM(VOLUME)
FROM TABLE AS [Sum_Volume]
INTO [TABLE2]
Now two Questions:
POS
?TYPE
?I am using MSSQLServer.
Upvotes: 1
Views: 132
Reputation: 96055
What you're looking for is to use GROUP BY
, along with your Aggregate functions. So, this results in:
USE Sandbox;
GO
CREATE TABLE Table1 (Pos tinyint, [Type] char(1), Volume smallint);
INSERT INTO Table1
VALUES (1,'A',34 ),
(2,'A',2 ),
(1,'A',12 ),
(3,'B',200),
(4,'C',1 );
GO
SELECT Pos,
COUNT(CASE WHEN [Type] = 'A' THEN [Type] END) AS Amount_A,
COUNT(CASE WHEN [Type] = 'B' THEN [Type] END) AS Amount_B,
COUNT(CASE WHEN [Type] = 'C' THEN [Type] END) AS Amount_C,
SUM(Volume) As Sum_Volume
FROM Table1 T1
GROUP BY Pos;
DROP TABLE Table1;
GO
if you have a variable, and undefined, number of values for [Type]
, then you're most likely going to need to use Dynamic SQL.
Upvotes: 4
Reputation: 181
your first column should be POS, and you'll GROUP BY POS.
This will give you one row for each POS value, and aggregate (COUNT and SUM) accordingly.
You can also use CASE statements instead of subselects. For instance, instead of:
(SELECT COUNT(TYPE)
FROM TABLE1
WHERE TYPE = 'A') AS [Amount_A]
use:
COUNT(CASE WHEN TYPE = 'A' then 1 else NULL END) AS [Amount_A]
Upvotes: 0