0

I have 2 Tables named Product, Stock as follows Product Table:-

Id     Name     Hand
1      A        50
2      B        5
3      C        10

Stock Table:-

Id    Pid     Qty
1     1       50

I want total stock of every product so hand column showing the opening stock. I tried:

select product.id, name, hand, count(qty) 
from product 
left join stock on pid=product.id

but it's giving me only the first product detail even i am doing left join

forpas
  • 160,666
  • 10
  • 38
  • 76
vivek modi
  • 800
  • 2
  • 7
  • 23
  • Does this answer your question? [Why does COUNT() show only one row of table?](https://stackoverflow.com/questions/39444904/why-does-count-show-only-one-row-of-table) – philipxy Feb 16 '20 at 10:18
  • This is a faq. It's clear that this is going to be a faq Before considering posting please always google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. – philipxy Feb 16 '20 at 10:18

1 Answers1

1

You must group by product and use sum() instead of count():

select p.id, p.name, p.hand, coalesce(sum(qty), 0) totalstock
from product p left join stock s
on s.pid = p.id
group by p.id, p.name, p.hand

See the demo.
Results:

| id  | name | hand | totalstock |
| --- | ---- | ---- | ---------- |
| 1   | A    | 50   | 50         |
| 2   | B    | 5    | 0          |
| 3   | C    | 10   | 0          |
forpas
  • 160,666
  • 10
  • 38
  • 76