CODEWITHSUNDEEP
sqlsql-servercount
Renascent
Renascent

Reputation: 184

how to get COUNT of a column against a set of specific values of multiple columns?

+------+-------+-------+---------+---------+
| R_ID |   BU  |  DIV  | Product | C_Func  |
+------+-------+-------+---------+---------+
|  1   |  BU1  | Div1  |   P1    |  Dev    |
|  2   |  BU2  | Div2  |   P2    |  Dev    |
|  3   |  BU2  | Div2  |   P2    |  BA     |
|  4   |  BU3  | Div3  |   P3    |  Dev    |
|  5   |  BU3  | Div3  |   P3    |  QA     |
|  6   |  BU4  | Div4  |   P4    |  Dev    |
|  7   |  BU4  | Div4  |   P4    |  QA     |
|  8   |  BU4  | Div4  |   P4    |  BA     |
|  9   |  BU1  | Div1  |   P1    |  Dev    |
|  10  |  BU2  | Div2  |   P2    |  Dev    |
|  11  |  BU2  | Div2  |   P2    |  BA     |
|  12  |  BU3  | Div3  |   P3    |  Dev    |
|  13  |  BU3  | Div3  |   P3    |  QA     |
|  14  |  BU4  | Div4  |   P4    |  Dev    |
|  15  |  BU4  | Div4  |   P4    |  QA     |
|  16  |  BU4  | Div4  |   P4    |  BA     |
|  17  |  BU3  | Div3  |   P3    |  QA     |
|  18  |  BU4  | Div4  |   P4    |  Dev    |
|  19  |  BU4  | Div4  |   P4    |  QA     |
|  20  |  BU4  | Div4  |   P4    |  BA     |
+------+-------+-------+---------+---------+

I have data in a table as shown above which i fetched using sql joins and all. what I want is the number of count of 'R_ID' working on set of (BU,DIV,Product,C_func). for e.g. I want to know how many 'R_ID' are there for (BU1,Div1,P1,Dev) for that the count should be 2 as there are 2 entries for the set (BU1,Div1,P1,Dev).

Expected output:

 +-------+-------+---------+---------+-------+
 |   BU  |  DIV  | Product | C_Func  | Count |
 +-------+-------+---------+---------+-------+
 |  BU1  | Div1  |   P1    |  Dev    |   2   |
 |  BU2  | Div2  |   P2    |  Dev    |   2   |
 |  BU2  | Div2  |   P2    |  BA     |   2   |
 |  BU3  | Div3  |   P3    |  Dev    |   2   |
 |  BU3  | Div3  |   P3    |  QA     |   3   |
 |  BU4  | Div4  |   P4    |  Dev    |   3   |
 |  BU4  | Div4  |   P4    |  QA     |   3   |
 |  BU4  | Div4  |   P4    |  BA     |   3   |       
 +-------+-------+---------+---------+-------+

Please help me. Thanks in advance.

Upvotes: 0

Views: 62

Answers (6)

Jack
Jack

Reputation: 99

Try this:

select   BU, DIV, Product, C_Func, count(1)
from     table
group by BU, DIV, Product, C_Func

Upvotes: 0

Nolan Shang
Nolan Shang

Reputation: 2328

;WITH tb(R_ID,BU,DIV,Product,C_Func)AS(
    SELECT 1,'BU1','Div1','P1','Dev' UNION ALL
    SELECT 2,'BU2','Div2','P2','Dev' UNION ALL
    SELECT 3,'BU2','Div2','P2','BA' UNION ALL
    SELECT 4,'BU3','Div3','P3','Dev' UNION ALL
    SELECT 5,'BU3','Div3','P3','QA' UNION ALL
    SELECT 6,'BU4','Div4','P4','Dev' UNION ALL
    SELECT 7,'BU4','Div4','P4','QA' UNION ALL
    SELECT 8,'BU4','Div4','P4','BA' UNION ALL
    SELECT 9,'BU1','Div1','P1','Dev' UNION ALL
    SELECT 10,'BU2','Div2','P2','Dev' UNION ALL
    SELECT 11,'BU2','Div2','P2','BA' UNION ALL
    SELECT 12,'BU3','Div3','P3','Dev' UNION ALL
    SELECT 13,'BU3','Div3','P3','QA' UNION ALL
    SELECT 14,'BU4','Div4','P4','Dev' UNION ALL
    SELECT 15,'BU4','Div4','P4','QA' UNION ALL
    SELECT 16,'BU4','Div4','P4','BA' UNION ALL
    SELECT 17,'BU3','Div3','P3','QA' UNION ALL
    SELECT 18,'BU4','Div4','P4','Dev' UNION ALL
    SELECT 19,'BU4','Div4','P4','QA' UNION ALL
    SELECT 20,'BU4','Div4','P4','BA'
    )
    SELECT BU,DIV,Product,C_Func,COUNT(DISTINCT R_ID) AS [R_ID_Count] 
    FROM tb GROUP BY BU,DIV,Product,C_Func

BU   DIV  Product C_Func R_ID_Count
---- ---- ------- ------ -----------
BU1  Div1 P1      Dev    2
BU2  Div2 P2      BA     2
BU2  Div2 P2      Dev    2
BU3  Div3 P3      Dev    2
BU3  Div3 P3      QA     3
BU4  Div4 P4      BA     3
BU4  Div4 P4      Dev    3
BU4  Div4 P4      QA     3

Upvotes: 0

FDavidov
FDavidov

Reputation: 3675

There are two alternative approaches, depending on the uniqueness of R_ID. If it is defined as a UNIQUE INDEX, then you can simply count the rows as:

SELECT BU1 , COUNT(*)
  FROM YourTable
 GROUP BY BU1   ;

If it is not define as a UNIQUE INDEX and you want to know how many different indices are, the query then becomes:

SELECT BU1 , COUNT(DISTINCT R_ID)
  FROM YourTable
 GROUP BY BU1   ;

The same type of query can be used for the other columns.

Upvotes: 0

Mansoor
Mansoor

Reputation: 4192

SELECT BU,DIV,Product,C_Func,COUNT(*)
FROM YOURTABLE
GROUP BY BU,DIV,Product,C_Func

Upvotes: 0

Sateesh Pagolu
Sateesh Pagolu

Reputation: 9606

This is a simple GROUP BY scenario.

SELECT BU,DIV,Product,C_Func,COUNT(R_ID)
FROM YOURTABLE
GROUP BY BU,DIV,Product,C_Func

You need to group by all those columns which you want to see them as groups and apply count on the left out column.

Upvotes: 1

trincot
trincot

Reputation: 350270

You can do that like this:

select   BU, DIV, Product, C_Func, count(*)
from     mytable
group by BU, DIV, Product, C_Func

Upvotes: 1

Related Questions