CODEWITHSUNDEEP
sqlt-sql
kevin
kevin

Reputation: 14095

How to get the Count of the subgroup in SQL

How can I get the Count of the subgroup. I have Table1 and Table2 (below). How can I get the table Result. I tired group by but only manage to get the total rows. I am trying it in Microsoft SQL.

Table1
ID  PriID   Function
1   500     A
2   500     B
3   500     C
4   501     A
5   501     B
6   502     C
7   503     A


Table2
PriID   Color
500     Green
501     Red
502     Green
503     Green


Result
Function    Green       Red     Total
A           2           1       3
B           1           1       2
C           2           0       2

below is the current one I tried

SELECT t1.Function, Count(t1.PriID) as Total FROM Table1 t1 
LEFT JOIN Table2 t2 on t1.PriID = t2.PriID
GROUP BY t1.function

and I get this

Result
Function    Total
A           3
B           2
C           2

Upvotes: 2

Views: 4490

Answers (6)

Prince Chawla
Prince Chawla

Reputation: 21

select [function], ISNULL(Green, 0) Green, ISNULL(Red, 0) Red,
       ISNULL(Green, 0) + ISNULL(Red, 0) Total 
from 
    (
        select *, count([function]) 'X' from 
        (
            select t2.color, t1.[function]  from table1 t1 
            JOIN table2 t2 on t1.prid = t2.prid 
        )A 
        group by color, [function] 
    )B pivot (max(X) for color IN ([Green],[Red]))as d

Upvotes: 2

Microlang
Microlang

Reputation: 534

The best way for all records in table (If you edit, delete or insert new one) is:

 

    With  t (F,PriID,Color) as 
    (
    SELECT     Table1.F, Table2.PriID, Table2.Color
    FROM         Table1 INNER JOIN
                          Table2 ON Table1.PriID = Table2.PriID
    GROUP BY Table2.PriID,F,Color) 
    select F,Color,COUNT(F)  as total from t group by F,Color

And you get this:

Result

Function    Color   total
A           Green   2
B           Green   1
C           Green   2
A           Red     1
B           Red     1

Upvotes: 1

user3864233
user3864233

Reputation: 514

This is the best approach for doing this stuff

;with cte as
(
select [Function],t1.priID,color
     from tbl1 t1 
        join tbl2 t2 on t1.priID = t2.priid
),def as
(
    select [Function],[Green],[Red] 
    from 
    (
        select [Function],color,priID  from cte) dk
        Pivot (count(priID) for color in([Green],[Red] )
     ) pvt 
) select *,green+red [Total] from def

Upvotes: 2

Almazini
Almazini

Reputation: 1873

Hi you should use PIVOT to get result. Like this:

CREATE TABLE #table (id int, priID int, [FUNCTION] varchar(5))

INSERT INTO #table ( id,priID,[FUNCTION] )
VALUES  (  1, 500,'a' ), (  2, 500,'b' ), (  3, 500,'c' ), (  4, 501,'a' ), (  5, 501,'b' ), (  6, 502,'c' )


CREATE TABLE #table2 (priID int, color varchar(20))

INSERT INTO #table2 (priID,color )
VALUES  (  500,'green' ), (  501, 'red' ), (  502,'yellow' )


SELECT t.[FUNCTION], t2.color, COUNT(t.priID) AS cnt 
INTO #result
FROM #table AS T
INNER JOIN #table2 AS T2 ON T2.priID = T.priID
GROUP BY t.[FUNCTION], t2.color


SELECT [FUNCTION], green, red, yellow
FROM (  SELECT [FUNCTION], color, cnt
        FROM #result AS R) AS p
PIVOT ( MAX(cnt) FOR color IN (green, red, yellow)) AS pvt

http://rextester.com/ROPBBS57014

Upvotes: 1

shree.pat18
shree.pat18

Reputation: 21757

Try this:

;with cte as
(select [function], 
 Sum(CASE WHEN t1.PriId <> 501 Then 1 Else 0 END) over(partition by [function] order by t1.priid) Green,
 Sum(CASE WHEN t1.PriId = 501 Then 1 Else 0 END) over(partition by [function] order by t1.priid) Red,
 Count(*) over (partition by [function] order by t1.priid) Total,
 Row_Number() over (partition by [function] order by t1.priid desc) rn
 from
 table1 t1 
 left join table2 t2 on t1.priid = t2.priid)

select [function],green,red,total
from cte
where rn = 1

This query uses windowing functions (available from SQL Server 2005 onwards), which eliminates the need for a GROUP BY clause

Demo

Upvotes: 1

schlonzo
schlonzo

Reputation: 1404

Try this

SELECT t1.Function, 
SUM(CASE WHEN Color = 'Green' THEN 1 ELSE 0 END) AS Green, 
SUM(CASE WHEN Color = 'Red' THEN 1 ELSE 0 END) AS Red, 
Count(t1.PriID) as Total FROM Table1 t1 
LEFT JOIN Table2 t2 on t1.PriID = t2.PriID
GROUP BY t1.function

Upvotes: 3

Related Questions