Sql rows count with grouping

Question

Hello I have two tables as below

tblContactType

typeId  typeName active
1       Email      1
2       Phone      1
3       Address    1
4       Fax        1

tblContact

id    IdName   typeId  groupId
100    test      1       1
101    test2     1       1
102    test3     1       2
103    test4     2       2
104    test5     2       3
105    test6     3       3

Want the results to be with column names as typeName count and grouped by group id. Results should be total number of types associated to a group,which are associated to a contact.

GroupId    EmailCount    PhoneCount  AddressCount     FaxCount
1           2             0             0               0
2           1             1             0               0
3           0             1             1               0

what have you tried so far? you can achieve this by LEFT JOIN, GROUP BY, SUM and IIF or CASE — RoMEoMusTDiE
– RoMEoMusTDiE, Commented Jun 11, 2017 at 21:52

Kannan Kandasamy · Accepted Answer · 2017-06-11 23:35:00Z

1

You can group by and pivot as below:

Select * from (
    Select t.groupid, tct.typename, t.id from tblContact t 
    inner join tblContactType tct 
    on t.typeid = tct.typeid
) a
pivot (count(a.id) for typename in ([Email],[Phone],[Address],[Fax]) ) p

For dynamic list of columns you can use dynamic query as below:

declare @cols1 varchar(max)
declare @query nvarchar(max)

Select  @cols1 = stuff((Select distinct ','+QuoteName(typename) from tblContactType for xml path('')),1,1,'')
Set     @query = '  Select * from (
        Select t.groupid, tct.typename, t.id from tblContact t 
        inner join tblContactType tct 
        on t.typeid = tct.typeid
    ) a
    pivot (count(a.id) for typename in (' + @cols1 + ') ) p '

Select @query --Check the generated query is good and then execute below
--exec sp_executesql @query

Output as below:

+---------+---------+-------+-----+-------+
| groupid | Address | Email | Fax | Phone |
+---------+---------+-------+-----+-------+
|       1 |       0 |     2 |   0 |     0 |
|       2 |       0 |     1 |   0 |     1 |
|       3 |       1 |     0 |   0 |     1 |
+---------+---------+-------+-----+-------+

edited Jun 11, 2017 at 23:35

answered Jun 11, 2017 at 23:27

Kannan Kandasamy

14k3 gold badges28 silver badges40 bronze badges

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

1 Comment

user3038399 Over a year ago

This works perfectly fine thanks. Also how can I add a total column and display total of each row in that column??

Jason · Accepted Answer · 2017-06-11 23:51:11Z

Here is another solution.

SELECT groupId,
        SUM(CASE WHEN c.typeId = 1 THEN 1 ELSE 0 END) 'EmailCount',
        SUM(CASE WHEN c.typeId = 2 THEN 1 ELSE 0 END) 'PhoneCount',
        SUM(CASE WHEN c.typeId = 3 THEN 1 ELSE 0 END) 'AddressCount',
        SUM(CASE WHEN c.typeId = 4 THEN 1 ELSE 0 END) 'FaxCount'
FROM tblContact c
    JOIN tblContactType ct ON c.typeId = ct.typeId  
GROUP BY groupId

Results

-------------------------------------------------------------
groupId  |  EmailCount | PhoneCount | AddressCount | FaxCount
-------------------------------------------------------------
  1      |     2       |    0       |      0       |    0
  2      |     1       |    1       |      0       |    0
  3      |     0       |    1       |      1       |    0
-------------------------------------------------------------

Collectives™ on Stack Overflow

Sql rows count with grouping

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related