CODEWITHSUNDEEP
sqlsql-serverjoin
Sachith Wickramaarachchi
Sachith Wickramaarachchi

Reputation: 5862

Get count of each locations by status

I have two tables called QueueTable and Location,

QueueTable data as follows,

QueueID FkBranchID  AddedTime   Status
11001           10  2019-07-02  5
11002           10  2019-07-03  5
11003           10  2019-07-04  6
11004           10  2019-07-05  5
11005           20  2019-07-06  6
11006           20  2019-07-07  5
11007           20  2019-07-08  6
11008           20  2019-07-09  6
11009           20  2019-07-10  5
11010           30  2019-07-11  5
11011           30  2019-07-12  6
11012           30  2019-07-13  5
11013           30  2019-07-14  6
11014           30  2019-07-15  5
11015           40  2019-07-16  5
11016           40  2019-07-17  5
11017           40  2019-07-02  5
11018           40  2019-07-03  5
11019           40  2019-07-04  6
11020           50  2019-07-05  5
11021           50  2019-07-06  5
11022           50  2019-07-13  6

And Location table data as follows

BranchID    BranchName
10          Delhi
20          Karnataka
30          Telangana
40          Gujarat
50          Tamil Nadu

Now I need to retrieve the following output by joining tables

enter image description here

I tried this query and I know there are more wrong things,How to solve this

select b.BranchId AS ID, b.BranchName,count(lo.QueueID) as Count1 
from Location b
left outer join
(
select br.BranchId,l.QueueID 
from QueueTable l
LEFT OUTER join Location br 
on l.FkBranchId = br.BranchId
where  l.IsActive = 1
GROUP BY l.QueueID) lo on lo.BranchId = b.BranchId

But it throw an error

Column 'Branch.BranchId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Upvotes: 3

Views: 154

Answers (4)

mkRabbani
mkRabbani

Reputation: 16908

Try this-

SELECT L.BranchID,
L.BranchName,
COUNT(CASE WHEN Status = 5 THEN 1 ELSE NULL END) [Count1(5)], 
COUNT(CASE WHEN Status = 6 THEN 1 ELSE NULL END) [Count2(6)]
-- You can add more Column here based on Number of Status you have.
FROM Location L
LEFT JOIN QueueTable Q ON L.BranchID = Q.FKBranchID
WHERE Q.IsActive = 1
GROUP BY L.BranchID,L.BranchName

Upvotes: 1

Shushil Bohara
Shushil Bohara

Reputation: 5656

USE dynamic query to count the status specially when it's dynamic

DECLARE @col VARCHAR(1000)
DECLARE @sql VARCHAR(2000)

SELECT @col = COALESCE(@col + ', ','') + QUOTENAME(CAST([status] AS VARCHAR))
FROM #QueueTable
GROUP BY [status]

SET @sql = '
select BranchID, BranchName, ' + @col + '
from (
    select l.BranchID, l.BranchName, FkBranchID, q.status
    from #Location l 
    join #QueueTable q on q.FkBranchID=l.BranchID)p
PIVOT(COUNT(FkBranchID) FOR status IN ( ' + @col + ' )
) AS pvt
ORDER BY BranchID'

PRINT @sql
EXEC (@sql)

OUTPUT:

BranchID    BranchName  5   6
10          Delhi       3   1
20          Karnataka   2   3
30          Telangana   3   2
40          Gujarat     4   1
50          Tamil Nadu  2   1

Upvotes: 0

Ahmad
Ahmad

Reputation: 12737

One simple way is to this:

select
     a.branchid,
     a.branchname,
     (select count(*) from queuetable where fkbranchid=a.branchid and status=5) status_5_count,
     (select count(*) from queuetable where fkbranchid=a.branchid and status=6) status_6_count

from
     location a

You can execute this query live on this SqlFiddle

Upvotes: 1

forpas
forpas

Reputation: 164099

Join the tables, group by BranchId, BranchName an count conditionally with a CASE statement the occurrences of 5 and 6:

select 
  l.BranchId AS ID, 
  l.BranchName,
  count(case q.Status when 6 then 1 end) as Count1,
  count(case q.Status when 5 then 1 end) as Count2  
from Location l left join QueueTable q
on l.BranchId = q.FkBranchId
where l.IsActive = 1
group by l.BranchId, l.BranchName

Upvotes: 1

Related Questions