Ganthur
Ganthur

Reputation: 41

SQL Union with counts

I have a view that when executed returns the count of how many times a code is being referenced. My view is :

    Create view [myview] as
    SELECT S.id AS code_id,
    u.asset_standard_id,
    Count(U.pkey) AS COUNT
    FROM imtbl_code S LEFT OUTER JOIN 
    imvw_woap_code_with_cust U ON U.code_id = S.Id and s.code_type = (select pkey from imtbl_code_type where imtbl_code_type.id = 'A-Problem') 
    GROUP BY S.id,
    u.asset_standard_id
    order by count

so if my tables are set up like the following :

    imvw_woap_code_with_cust       ||  imtbl_code
    ---------------------------------
    Code_id  | asset_standard_id   ||  Code_id
    ----------------------------------------------
    c1      25209-45MEO            ||  c1
    c3      25209-45MEO            ||  c2
    c3      25209-45MEO            ||  c3

when i run select * from myview where asset_standard_id = '25209-45MEO' the view returns

   code_id || asset_standard_id || count
    c3           25209-45MEO         2
    c1           25209-45MEO         1

what i would like to see is values returned when the count is zero as well.

   code_id || asset_standard_id || count
   c3           25209-45MEO         2
   c1           25209-45MEO         1
   c2           25209-45MEO         0

I asked a similar question previously but wasnt clear, just trying to clarify a bit more.

I wanted to add from the answers that this code :

SELECT
        possibilities.ID AS code_id,
        possibilities.asset_standard_id,
        Count(U.pkey) AS [COUNT]
FROM (
    SELECT
        a.asset_standard_id,b.ID
    FROM (SELECT DISTINCT asset_standard_id FROM imvw_woap_code_with_cust) AS a
    CROSS JOIN (
        SELECT DISTINCT ID FROM imtbl_code
        UNION
        SELECT DISTINCT CODE_ID AS [ID] FROM imvw_woap_code_with_cust 
    ) AS b
) AS possibilities
LEFT OUTER JOIN imvw_woap_code_with_cust AS U ON U.code_id = possibilities.Id
    and u.code_type = (
        select pkey
        from imtbl_code_type 
        where imtbl_code_type.id = 'A-Problem'
    ) 
where u.code_type_id = 'A-Problem' 
GROUP BY possibilities.id, possibilities.asset_standard_id

was the closed but the counts are way off.

SAMPLE DATA FROM TALBES imvw_woap_code_with_cust

pkey         code_id           code_type_id,        asset_standard_id
101480  Alarmining                 A-Problem          1 East
103350  Fluid/Gas Leak (Out)       A-Problem           2 west
104033  plug/Pwr Cord Damg         A-Problem         2 west
107296  Alarmining                 A-Problem         2 west
106404  Incoming Inspect           A-Problem         2 west
106407  Incoming Inspect           A-Problem          2 west

IMTBL_CODE

pkey,    code_id,                   code_type_id
100064  1-NO PROBLEM CODE ENTERED   A-Problem
100068  Alarmining                  A-Problem
100069  Battery Failure             A-Problem
100070  Incoming Inspect            A-Problem
100071  Fluid/Gas Leak (Out)        A-Problem
100072  Case Damaged                A-Problem
100073  plug/Pwr Cord Damg          A-Problem

Upvotes: 0

Views: 100

Answers (1)

UnhandledExcepSean
UnhandledExcepSean

Reputation: 12804

You need to get all possible combinations. CROSS JOIN works for this, but caution: it is painful on large datasets.

DECLARE @imvw_woap_code_with_cust TABLE (
    pkey INT,
    Code_id VARCHAR(200),
    code_type_id VARCHAR(200),
    asset_standard_id VARCHAR(200)
)

INSERT INTO @imvw_woap_code_with_cust(pkey,Code_id,code_type_id,asset_standard_id)
    SELECT 101480,'Alarmining','A-Problem','1 East'
    UNION SELECT 103350,'Fluid/Gas Leak (Out)','A-Problem','2 west'
    UNION SELECT 104033,'plug/Pwr Cord Damg','A-Problem','2 west'
    UNION SELECT 107296,'Alarmining','A-Problem','2 west'
    UNION SELECT 106404,'Incoming Inspect','A-Problem','2 west'
    UNION SELECT 106407,'Incoming Inspect','A-Problem','2 west'

DECLARE @IMTBL_CODE TABLE (
    pkey INT,
    Code_id VARCHAR(200),
    code_type_id VARCHAR(200)
)

INSERT INTO @IMTBL_CODE(pkey,Code_id,code_type_id)
    SELECT 100064,'1-NO PROBLEM CODE ENTERED','A-Problem'
    UNION SELECT 100068,'Alarmining','A-Problem'
    UNION SELECT 100069,'Battery Failure','A-Problem'
    UNION SELECT 100070,'Incoming Inspect','A-Problem'
    UNION SELECT 100071,'Fluid/Gas Leak (Out)','A-Problem'
    UNION SELECT 100072,'Case Damaged','A-Problem'
    UNION SELECT 100073,'plug/Pwr Cord Damg','A-Problem'

SELECT
        possibilities.code_id AS code_id,
        possibilities.asset_standard_id,
        Count(U.pkey) AS [COUNT]
FROM (
    SELECT
        a.asset_standard_id,b.code_id
    FROM (SELECT DISTINCT asset_standard_id FROM @imvw_woap_code_with_cust) AS a
    CROSS JOIN (
        SELECT DISTINCT code_id FROM @imtbl_code
        UNION
        SELECT DISTINCT CODE_ID AS [ID] FROM @imvw_woap_code_with_cust 
    ) AS b
) AS possibilities
LEFT OUTER JOIN @imvw_woap_code_with_cust AS U ON U.code_id = possibilities.code_id
    AND possibilities.asset_standard_id=u.asset_standard_id
    and u.code_type_id='A-Problem'
GROUP BY possibilities.code_id, possibilities.asset_standard_id
order by [count]

Upvotes: 1

Related Questions