Count Based on Columns in SQL Server

Question

I have 3 tables:

SELECT id, letter FROM As

+--------+--------+
|     id | letter |
+--------+--------+
|      1 | A      |
|      2 | B      |
+--------+--------+

SELECT id, letter FROM Xs

+--------+------------+
|     id |   letter   |
+--------+------------+
|      1 | X          |
|      2 | Y          |
|      3 | Z          |
+--------+------------+

SELECT id, As_id, Xs_id FROM A_X

+--------+-------+-------+
|     id | As_id | Xs_id |
+--------+-------+-------+
|      9 |     1 |     1 |
|     10 |     1 |     2 |
|     11 |     2 |     3 |
|     12 |     1 |     2 |
|     13 |     2 |     3 |
|     14 |     1 |     1 |
+--------+-------+-------+

I can count all As and Bs with group by. But I want to count As and Bs based on X,Y and Z. What I want to get is below:

+-------+
| X,Y,Z |
+-------+
| 2,2,0 |
| 0,0,2 |
+-------+

  X,Y,Z
A 2,2,0
B 0,0,2

What is the best way to do this at MSSQL? Is it an efficent way to use foreach for example?

edit: It is not a duplicate because I just wanted to know the efficent way not any way.

I am missing some kind of question. What do you want to achive? Please add some exlpanation. You can't excpect that people understand your problem with those view sentences and explanations. — Dom84, May 09 '16 at 13:59
I think he wants to join table As and Xs on A_X, do the count and pivot it to create a matrix ... But still it seems pretty simple join and dynamic pivot, best would be to toss what you have tried — Veljko89, May 09 '16 at 14:02
Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — Sean Lange, May 09 '16 at 14:07
I am sorry for missing information about the question. I want to count As and Bs based on X,Y and Z. I can do it with "while" in tsql but I just wanted to know the best way to do it. — Yavuz, May 09 '16 at 14:07
Possible duplicate of [SQL Multiple count on same row with dynamic column](http://stackoverflow.com/questions/17612471/sql-multiple-count-on-same-row-with-dynamic-column) — Tab Alleman, May 09 '16 at 14:11
it is not duplicate. I am trying to find the efficent way because of the performance of query. — Yavuz, May 09 '16 at 14:17
The solution in the duplicate question is the most efficient way. It's the same solution as proposed by all the answerers below. — Tab Alleman, May 09 '16 at 14:44
No, I think that what @TabAlleman is trying to say is that your question is a duplicate, and the answers to the other similar questions are also the most efficient. The tables and data are different, but the essence of the question you're asking is the same: "How do I pivot my data efficiently". Many other pivot related questions have the correct answer already. — EastOfJupiter, May 09 '16 at 15:08

EastOfJupiter · Accepted Answer · 2016-05-09T14:41:33.517

For what you're trying to do without knowing what is inefficient with your current code (because none was provided), a Pivot is best. There are a million resources online and here in the stack overflow Q/A forums to find what you need. This is probably the simplest explanation of a Pivot which I frequently need to remind myself of the complicated syntax of a pivot.

To specifically answer your question, this is the code that shows how the link above applies to your question

First Tables needed to be created

DECLARE @AS AS TABLE (ID INT, LETTER VARCHAR(1))
DECLARE @XS AS TABLE (ID INT, LETTER VARCHAR(1))
DECLARE @XA AS TABLE (ID INT, AsID INT, XsID INT)

Values were added to the tables

INSERT INTO @AS (ID, Letter)
SELECT 1,'A'
UNION   
SELECT 2,'B'


INSERT INTO @XS (ID, Letter)
SELECT 1,'X'
UNION   
SELECT 2,'Y'
UNION   
SELECT 3,'Z'

INSERT INTO @XA (ID, ASID, XSID)
SELECT 9,1,1
UNION
SELECT 10,1,2
UNION
SELECT 11,2,3
UNION
SELECT 12,1,2
UNION
SELECT 13,2,3
UNION
SELECT 14,1,1

Then the query which does the pivot is constructed:

SELECT   LetterA, [X],[Y],[Z]
  FROM  (SELECT  A.LETTER AS LetterA
                ,B.LETTER AS LetterX
                ,C.ID
          FROM   @XA C
          JOIN   @AS A
            ON   A.ID = C.ASID
          JOIN   @XS B
            ON   B.ID = C.XSID
        ) Src
 PIVOT  (COUNT(ID)
   FOR   LetterX IN ([X],[Y],[Z])
        ) AS PVT

When executed, your results are as follows:

Letter  X   Y   Z
A       2   2   0
B       0   0   2

hmm that's actually better pivot then mine ... didn't think to do the count inside of pivot, rather pivot the data after inner select / calculation, but your way seems more clear — Veljko89, May 09 '16 at 14:36

score 0 · Answer 2 · answered May 09 '16 at 14:32

As i said in comment ... just join and do simple pivot

if object_id('tempdb..#AAs') is not null drop table #AAs
create table #AAs(id int, letter nvarchar(5))

if object_id('tempdb..#XXs') is not null drop table #XXs
create table #XXs(id int, letter nvarchar(5))

if object_id('tempdb..#A_X') is not null drop table #A_X
create table #A_X(id int, AAs int, XXs int)

insert into #AAs (id, letter) values (1, 'A'), (2, 'B')
insert into #XXs (id, letter) values (1, 'X'), (2, 'Y'),  (3, 'Z')
insert into #A_X (id, AAs, XXs) 
values (9,  1, 1), 
       (10, 1, 2), 
       (11, 2, 3),
       (12, 1, 2), 
       (13, 2, 3), 
       (14, 1, 1)

select LetterA, 
       ISNULL([X], 0) [X], 
       ISNULL([Y], 0) [Y], 
       ISNULL([Z], 0) [Z]
from (
    select distinct a.letter [LetterA], x.letter [LetterX],
           count(*) over (partition by a.letter, x.letter order by a.letter) [Counted]
        from #A_X ax
    join #AAs A on ax.AAs = A.ID
    join #XXs X on ax.XXs = X.ID
)src
PIVOT
(
    MAX ([Counted]) for LetterX in ([X], [Y], [Z])
) piv

You get result as you asked for

LetterA X   Y   Z
   A    2   2   0
   B    0   0   2

Count Based on Columns in SQL Server

2 Answers2