Clever Geek Handbook

TSQL for counting fields and summing

Lack of experience with this kind of consolidation, but I expect it to be a routine (I hope so). It counts the columns thrown at me. Actual data - ~ 20 thousand lines:

Data format:

State   Owner   Job1    Job2    Job3    Job4
TN      Joe     123     456             234 
TN      Frank           456     789     
FL      Joe     123     456
FL      Frank   123

Needed results:

State Owner JobCount
TN  Joe     3
TN  Frank   2
FL  Joe     2
FL  Frank   1

And wrapped up to the owner

Owner   JobCount
Joe     5
Frank   3
+3


source to share


4 answers


Here is your TSQL for result 1



SELECT 
    State
    ,Owner 
    ,Sum (
    (
        CASE 
            WHEN Job1 IS NULL THEN 0 
            ELSE 1 
        END)+
        (CASE 
            WHEN  Job2 IS NULL THEN 0 
            ELSE 1 
        END) + 
        (CASE 
            WHEN Job3 IS NULL THEN 0 
            ELSE 1 
        END)+
        (CASE 
            WHEN Job4 IS NULL THEN 0 
            ELSE 1 
        END))
FROM table
GROUP BY State, OWNER
+1


source


I think PIVOT is the best fit as the number of jobs can increase:

;WITH cte AS 
(SELECT [State]
      ,[Owner]
      ,[Job]
      ,[JobN]
FROM (
        SELECT
             [State]
            ,[Owner]
            ,Job1
            ,Job2
            ,Job3
            ,Job4
        FROM #state
     ) AS p
UNPIVOT
(JobN FOR [Job] IN
(Job1,Job2,Job3,Job4)
) AS unpvt)
--SELECT [State], [Owner], COUNT(1) AS JobCount
--FROM cte
--GROUP BY [State], [Owner]
SELECT [Owner], COUNT(1) AS JobCOunt
FROM cte
GROUP BY [Owner]


The lines requested is the first requested request. I first created a temporary #state table like this:

CREATE TABLE #state
(
    [State] VARCHAR(2)
    ,[Owner] VARCHAR(20)
    ,[Job1] INT
    ,[Job2] INT
    ,[Job3] INT
    ,[Job4] INT
)
+2


source


For the state / owner

select  State, Owner, count(cs.Jobs) as JobCount
from yourtable 
cross apply (values (Job1),(Job2),(Job3),(Job4)) cs (Jobs)
Group By State, Owner

Inverted owner

select Owner, count(cs.Jobs) as JobCount
from yourtable 
cross apply (values (Job1),(Job2),(Job3),(Job4)) cs (Jobs)
Group by Owner

Note. ... In this case, it is considered that empty values NULL

in the sample data in the table

+1


source


Another option ... just for fun GROUP KITS

You will get the Owner / Condition level and the Owner level in one shot

Select [Owner]
      ,[State]
      ,JobCount = sum(isnull(sign(Job1),0)+isnull(sign(Job2),0)+isnull(sign(Job3),0)+isnull(sign(Job4),0))
From  YourTable
Group By Grouping Sets ([State],[Owner]),([Owner])
Order By case when [State] is null then 1 else 0 end

Returns

Owner   State   JobCount
Frank   FL      1
Frank   TN      2
Joe     FL      2
Joe     TN      3
Joe     NULL    5
Frank   NULL    3
+1


source






More articles:


All Articles