How use count for value from another table

Question

How to use count joining with different tables. Please have a look at my queries. Here i am using CROSS APPLY. But i am not getting the actual result. how i can to get all the item from item table not in incident table.

Tabel : Inc_cat

+------------+--------------+--+
| inc_cat_id |  inc_cat_n   |  |
+------------+--------------+--+
|          1 | Support      |  |
|          2 | PM           |  |
|          3 | Installation |  |
+------------+--------------+--+

Table:incident

+-------------+---------+------------+-----------------+
| incident_id | item_id | inc_cat_id | date_logged     |
+-------------+---------+------------+-----------------+
|         100 |     555 |          1 |  2016-01-01     |
|         101 |     555 |          2 |  2016-01-18     |
|         103 |     444 |          3 |  2016-02-10     |
|         104 |     444 |          2 |  2016-04-01     |
|         105 |     666 |          1 |  2016-04-09     |
|         106 |     555 |          2 |  2016-04-20     |
+-------------+---------+------------+-----------------+

Table:item

+---------+---------+--+
| item_id | cust_id |  |
+---------+---------+--+
|     444 |      34 |  |
|     555 |      34 |  |
|     666 |      76 |  |
|     333 |      34 |  |
|     222 |      34 |  |
|     111 |      34 |  |
+---------+---------+--+

Result:

+---------+----------------+-----------+---------------------+
| item_id | count(Support) | count(PM) | count(Installation) |
+---------+----------------+-----------+---------------------+
|     555 |              0 |         1 |                   0 |
|     444 |              0 |         1 |                   0 |
|     666 |              0 |         0 |                   0 |
|     333 |              0 |         0 |                   0 |
|     222 |              0 |         0 |                   0 |
|     111 |              0 |         0 |                   0 |
+---------+----------------+-----------+---------------------+

My Query:

 SELECT i.item_ID, 
 COUNT(CASE WHEN i.inc_cat_id = ic.inc_cat_id AND i.inc_cat_id = 1 THEN 1 END) AS cntSupport,
 COUNT(CASE WHEN i.inc_cat_id = ic.inc_cat_id AND i.inc_cat_id = 2 THEN 1 END) AS cntPM,
 COUNT(CASE WHEN i.inc_cat_id = ic.inc_cat_id AND i.inc_cat_id = 3 THEN 1 END) AS cntInstallation
 FROM @incident i
 CROSS APPLY @incCat ic
 WHERE (i.date_logged BETWEEN '2016-04-01' AND '2016-04-30')AND i.cust_id='34'
 GROUP BY i.item_ID

Answer 1

You don't need a CROSS APPLY. A simple LEFT JOIN will do:

SELECT i.item_id,
       COUNT(CASE WHEN inc.inc_cat_id = 1 THEN 1 END) AS cntSupport,
       COUNT(CASE WHEN inc.inc_cat_id = 2 THEN 1 END) AS cntPM,
       COUNT(CASE WHEN inc.inc_cat_id = 3 THEN 1 END) AS cntInstallation
FROM Item AS i
LEFT JOIN Incident AS inc ON i.item_id = inc.item_id AND
                      inc.date_logged BETWEEN '2016-04-01' AND '2016-04-30'        
WHERE i.cust_id = 34
GROUP BY i.item_id

You just need to start by table Item, so as to get all items returned, as in the expected result set in the OP.

Demo here

Answer 2

Unless I'm missing something, your query is over complicated:

SELECT item_ID, 
       COUNT(CASE WHEN .inc_cat_id = 1 THEN 1 END) AS cntSupport,
       COUNT(CASE WHEN inc_cat_id = 2 THEN 1 END) AS cntPM,
       COUNT(CASE WHEN inc_cat_id = 3 THEN 1 END) AS cntInstallation
  FROM @incident 
 GROUP BY item_ID

Answer 3

UPDATE:

With the addition of logged_date column, you just need to add it in the ON clause. You need to use sp_executesql instead of EXEC now to prevent sql injection:

DECLARE @fromDate DATE = '2016-04-01',
        @toDate DATE = '2016-04-30';

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql =
'SELECT
    i.item_id' + CHAR(10) +
(SELECT
'   , COUNT(CASE WHEN inc.inc_cat_id = ' + CONVERT(VARCHAR(10), inc_cat_id) + 
' THEN 1 END) AS ' + QUOTENAME('count(' + inc_cat_n + ')') + CHAR(10)
FROM #Inc_cat
ORDER BY inc_cat_id
FOR XML PATH('')
) +
'FROM #item AS i
LEFT JOIN #incident AS inc
    ON i.item_id = inc.item_id
    AND inc.date_logged BETWEEN @fromDate AND @toDate
GROUP BY i.item_id;';

PRINT (@sql);
EXEC sp_executesql
        @sql,
        N'@fromDate DATE, @toDate DATE',
        @fromDate,
        @toDate

ONLINE DEMO

---

Giorgos answer is good if you only have that 3 Inc_cats. However, if you have unknown number of Inc_cats, you need to do it dynamically. Here is a method using a dynamic crosstab:

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql =
'SELECT
    i.item_id' + CHAR(10) +
(SELECT
'   , COUNT(CASE WHEN inc.inc_cat_id = ' + CONVERT(VARCHAR(10), inc_cat_id) + 
' THEN 1 END) AS ' + QUOTENAME('count(' + inc_cat_n + ')') + CHAR(10)
FROM Inc_cat
ORDER BY inc_cat_id
FOR XML PATH('')
) +
'FROM Item AS i
LEFT JOIN Incident AS inc
    ON i.item_id = inc.item_id
GROUP BY i.item_id;';

PRINT (@sql);
EXEC (@sql);

ONLINE DEMO

Basically, it's just a dynamic version of Giorgos' answer.