Cyber Space Cyber Space - 5 months ago 15
SQL Question

Unable to figure out Inventory current stock calculation

I have 2 tables whose structure is as follows:

tblBookMst

Id bk_isbn bk_title bk_author
----------------------------------------------------
1 ISBN_001 ABC Book AA
2 ISBN_002 DEF Book BB
3 ISBN_003 GHI Book CC
4 ISBN_004 JKL Book DD


and

tblBookId
b_id id lib_id inv_stat
----------------------------------------------------
1 1 BK/LIB/01 1
2 1 BK/LIB/02 2
3 1 BK/LIB/03 2
4 2 BK/LIB/04 1
5 2 BK/LIB/05 1
6 3 BK/LIB/06 1


('inv_stat' legends: 1=> In Stock & 2 => In Circulation)
Using the above 2 tables, i want to write a query which will give me output as shown below

bk_title bk_author tot_copies in_stock in_circulation
ABC Book AA 3 1 2
DEF Book BB 2 2 0
GHI Book CC 1 1 0


Till now i have been unable to figure out how to calculate the 'in_stock' & 'in_circulation'.I am using the below mentioned sql query.

SELECT a.id,a.bk_title,a.bk_author,count(b.lib_id) as tot_copies
FROM tblBookMst a
JOIN tblBookId b ON a.id = b.id
GROUP BY a.id,a.bk_title,a.bk_author
ORDER BY a.bk_title


I hope you understand my question.Please advice with example

Answer

You are close! You just need some Case Statements:

SELECT a.id,
    a.bk_title,
    a.bk_author,
    count(b.lib_id) as tot_copies
    SUM(CASE WHEN b.inv_stat = 1 THEN 1 ELSE 0 END) as in_stock,
    SUM(CASE WHEN b.inv_stat = 2 THEN 1 ELSE 0 END) as in_circulation
FROM tblBookMst a
JOIN tblBookId b ON a.id = b.id
GROUP BY a.id,a.bk_title,a.bk_author
ORDER BY a.bk_title