Cyber Space - 1 year ago 60
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 Source

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
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download