CODEWITHSUNDEEP
sqlsql-server
Krypt
Krypt

Reputation: 421

Aggregation for multiple SQL SELECT statements

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:

  1. How can I include the distinction concerning POS?
  2. Is there any better way to count each TYPE?

I am using MSSQLServer.

Upvotes: 1

Views: 132

Answers (2)

Thom A
Thom A

Reputation: 96057

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

SQLCliff
SQLCliff

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

Related Questions