Home How to count in multiple columns a variable
Reply: 5

How to count in multiple columns a variable

ITCareMan
1#
ITCareMan Published in 2017-12-07 22:17:02Z

I am working on a Crystal Report and cannot figure out a way to do this in Crystal or MSSQL

I have a table with usernames that get recorded when an activity is completed in a web app like below

UID Approved    Denied  Moved   Pended
1   Tom         null    null    Bill
2   null        null    Bill    null
3   Bill        null    Tom     null
4   null        Tom     null    null
5   Tom         null    Bill    Bill
6   null        Bill    Bill    null
7   Tom         null    null    Bill

I need to produce a productivity report like below

        Approved    Denied  Moved   Pended
Tom     3           1       1       0
Bill    1           1       3       3

I am not sure how to group the counts by the names that are in the data?

Hadi
2#
Hadi Reply to 2017-12-08 00:06:36Z

SQL Fiddle Demo

First get all user into a temp table then group all aggregations into one table

--Get all users

SELECT *
INTO #Tbl_User
FROM (

SELECT DISTINCT Approved as [User]
FROM MY_TABLE

UNION 

SELECT  DISTINCT Denied  as [User]
FROM MY_TABLE

UNION 

SELECT DISTINCT  Moved as [User]
FROM MY_TABLE

UNION

SELECT DISTINCT Pended  as [User]
FROm MY_TABLE) AS T1 WHERE T1.User IS NOT NULL

--Group results

SELECT T1.[User], ISNULL(T2.Approved,0)  Approved, ISNULL(T3.Denied,0) Denied, ISNULL(T4.Moved,0) Moved, ISNULL(T5.Pended,0) Pended
FROM #Tbl_User AS T1 INNER JOIN (
SELECT Approved as [User],Count(*) as Approved    
FROM MY_TABLE
GROUP BY Approved) AS T2 ON T1.[User] =T2.[User]
LEFT JOIN (
SELECT Denied as [User],Count(*) as Denied    
FROM MY_TABLE
GROUP BY Denied) AS T3 ON T1.[User] =T3.[User]
LEFT JOIN (
SELECT Moved as [User],Count(*) as Moved    
FROM MY_TABLE
GROUP BY Moved) AS T4 ON T1.[User] =T4.[User]
LEFT JOIN (
SELECT Pended as [User],Count(*) as Pended    
FROM MY_TABLE
GROUP BY Pended) AS T5 ON T1.[User] =T5.[User]
Katherine Elizabeth Lightsey
3#
Katherine Elizabeth Lightsey Reply to 2017-12-07 22:34:05Z

If it isn't massive you can use chained CTE's:

DECLARE @activity AS TABLE
  (
       [uid]        INT
       , [approved] NVARCHAR(25)
       , [denied]   NVARCHAR(25)
       , [moved]    NVARCHAR(25)
       , [pending]  NVARCHAR(25)
  );

INSERT INTO @activity
            ([uid]
             , [approved]
             , [denied]
             , [moved]
             , [pending])
VALUES      (1,N'Curly',NULL,NULL,NULL),
            (2,NULL,NULL,N'Curly',NULL),
            (3,N'Curly',NULL,NULL,N'Mo'),
            (4,NULL,NULL,N'Joe',NULL),
            (5,N'Joe',NULL,N'Joe',NULL),
            (6,N'Joe',N'Shemp',N'Joe',NULL);

WITH [approved_builder]
     AS (SELECT [approved] AS [name]
                , count(*) AS [approved]
         FROM   @activity
         WHERE  [approved] IS NOT NULL
         GROUP  BY [approved])
   , [moved_builder]
     AS (SELECT [moved]    AS [name]
                , count(*) AS [moved]
         FROM   @activity
         WHERE  [moved] IS NOT NULL
         GROUP  BY [moved])
   , [denied_builder]
     AS (SELECT [denied]   AS [name]
                , count(*) AS [denied]
         FROM   @activity
         WHERE  [denied] IS NOT NULL
         GROUP  BY [denied])
   , [pending_builder]
     AS (SELECT [pending]  AS [name]
                , count(*) AS [pending]
         FROM   @activity
         WHERE  [pending] IS NOT NULL
         GROUP  BY [pending])
   , [aggregator]
     AS (SELECT [name]
                , [approved] AS [approved]
                , 0          AS [denied]
                , 0          AS [moved]
                , 0          AS [pending]
         FROM   [approved_builder]
         UNION ALL
         SELECT [name]
                , 0       AS [approved]
                , 0       AS [denied]
                , [moved] AS [moved]
                , 0       AS [pending]
         FROM   [moved_builder]
         UNION ALL
         SELECT [name]
                , 0        AS [approved]
                , [denied] AS [denied]
                , 0        AS [moved]
                , 0        AS [pending]
         FROM   [denied_builder]
         UNION ALL
         SELECT [name]
                , 0         AS [approved]
                , 0         AS [denied]
                , 0         AS [moved]
                , [pending] AS [pending]
         FROM   [pending_builder])
SELECT [name]            AS [name]
       , sum([approved]) AS [approved]
       , sum([denied])   AS [denied]
       , sum([moved])    AS [moved]
       , sum([pending])  AS [pending]
FROM   [aggregator]
GROUP  BY [name]; 
feiiiiii
4#
feiiiiii Reply to 2017-12-07 22:38:15Z

I think this simple query will do the trick to get the result you want.

SELECT Approved as Names, count(Approved) as Approved,
   (SELECT count(Denied)
    FROM tableName AS j2
    WHERE j1.Approved = j2.Denied) AS Denied,
    (SELECT count(Moved)
    FROM tableName AS j2
    WHERE j1.Approved = j2.Moved) AS Moved,
    (SELECT count(Pended)
    FROM tableName AS j2
    WHERE j1.Approved = j2.Pended) AS Pended
FROM tableName AS j1
GROUP BY Approved
DhruvJoshi
5#
DhruvJoshi Reply to 2017-12-07 22:51:03Z

you can also use pivot and unpivot syntax like below: See working demo

select 
Name,
[approved]=ISNULL([approved],0),
[denied]=ISNULL([denied],0),
[moved]=ISNULL([moved],0),
[pending]=ISNULL([pending],0) 
from
(
    select 
      Name,activity,count(1) c 
    from
    (select * from useractivity )s
    unpivot (Name for activity in ([approved],[denied],[moved],[pending]))up
    group by Name,activity
) s
pivot (sum(c) for activity in ([approved],[denied],[moved],[pending]))p
A.S.
6#
A.S. Reply to 2017-12-07 23:19:16Z

Yeah, it has to be UNPIVOTED and then PIVOTED. Here is how we can do this in Oracle sql.

SELECT *
FROM
  (SELECT name,
    status
  FROM report UNPIVOT( name FOR status IN (APPROVED AS 'APPROVED',DENIED AS 'DENIED', MOVED AS 'MOVED', PENDED AS 'PENDED'))
  ) PIVOT ( COUNT(status) FOR status   IN ('APPROVED','DENIED','MOVED','PENDED') )
ORDER BY name DESC;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.309071 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO