Fetch data from database and display in DataGridView

Question

I am using this query to fetch data from SQL Server 2008:

select 
    g.godown_name, s.supplier_name, t.type_name,
    r.bags_instock, b.lb_per_bag, t.rate
from 
    tbl_godown g, tbl_yarn_supplier s, tbl_yarn_type t,
    tbl_yarn_receive r, tbl_godown_transaction gt, tbl_yarn_book b
where 
    r.book_id = b.book_id and
    s.supplier_id = b.supplier_id and
    t.type_id = b.type_id and
    gt.godown_id = g.godown_id and
    gt.receive_id = r.receive_id

Result is that:

enter image description here

Here some transaction have same Godown, Suppliar, Tpe & lbs_per_bag

1. 1 & 7
2. 2,3,4,5
3. 8,9,10,11
4. 12
5. 6

if any row have same Godown, Suppliar, Tpe & lbs_per_bag they should not repeat

Result should be like that.

thanks

waiting for response

enter image description here

Looks like you need a simple GROUP BY clause

Kane
– Kane

2012-08-26 11:33:43 +00:00
Commented Aug 26, 2012 at 11:33 — Kane
– Kane, Commented Aug 26, 2012 at 11:33
might be ??? Revising it again by google it.

syed Ahsan Jaffri
– syed Ahsan Jaffri

2012-08-26 11:39:02 +00:00
Commented Aug 26, 2012 at 11:39 — syed Ahsan Jaffri
– syed Ahsan Jaffri, Commented Aug 26, 2012 at 11:39

RBarryYoung · Accepted Answer · 2012-08-26 15:28:05Z

2

This is how I would do it:

SELECT 
    g.godown_name, 
    s.supplier_name, 
    t.type_name,
    b.lb_per_bag, 
    SUM(r.bags_instock) As total_bags_instock, 
    SUM(t.rate * r.bags_instock) 
        /SUM(r.bags_instock) As average_rate

FROM tbl_godown_transaction  As gt
JOIN tbl_godown          As g  ON gt.godown_id   = g.godown_id 
JOIN tbl_yarn_receive    As r  ON gt.receive_id  = r.receive_id
JOIN tbl_yarn_book       As b  ON r.book_id      = b.book_id 
JOIN tbl_yarn_supplier   As s  ON s.supplier_id  = b.supplier_id 
JOIN tbl_yarn_type       As t  ON t.type_id      = b.type_id

GROUP BY
    g.godown_name, 
    s.supplier_name, 
    t.type_name,
    r.lb_per_bag

answered Aug 26, 2012 at 15:28

RBarryYoung

57k15 gold badges99 silver badges142 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Hassan Boutougha · Accepted Answer · 2012-08-26 15:18:43Z

1

prefer use inner join

 select 
        g.godown_name, s.supplier_name, t.type_name,
        r.bags_instock, b.lb_per_bag, t.rate
    from 
        tbl_godown g
        inner join tbl_godown_transaction gt
           on gt. gt.godown_id = g.godown_id 
        inner join  tbl_yarn_receive r
           on gt.receive_id = r.receive_id
        inner join tbl_yarn_book b
           on r.book_id = b.book_id
        inner join tbl_yarn_supplier s
           on  s.supplier_id = b.supplier_id
        inner join tbl_yarn_type t
           on t.type_id = b.type_id       
    group by    g.godown_name, s.supplier_name, t.type_name,
        r.bags_instock, b.lb_per_bag, t.rate

edited Aug 26, 2012 at 15:18

answered Aug 26, 2012 at 14:54

Hassan Boutougha

3,9191 gold badge19 silver badges19 bronze badges

1 Comment

RBarryYoung Over a year ago

That's not a valid query in SQL Server. All of the select columns have to be covered by group by or contained in aggregate functions.

Collectives™ on Stack Overflow

Fetch data from database and display in DataGridView

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related