How to minimized output rows in a query in my Windows Form Application

David Godistei

2y847C#

Greetings to all.

I am on-building a Windows Form Application where a stored procedure (build on two tables - Stock and Sales) is built on the database server (.mdf). Then, on the client side a form is built to pull data from the stored procedure on the server side.

The following is the SQL language used to build the stored procedure namely, AvailableQty:-

CREATE PROCEDURE [dbo].[AvailableQty]

       @code int = 0    

AS

       SELECT Stock.Category, Stock.ProductName, Stock.Model, Stock.StockQty, Sales.SaleQty FROM Stock, Sales

       WHERE Stock.Code = Sales.Code AND Stock.Code = @code

       RETURN 0

A form was built on the client side. Data were entered in both the stock and sales tables (two entries made in the stock table and three in the sales table) respectively. The AvailableQty form is to pull data per product, from both tables and display the result.

The results displayed were more than expected. I wish to attach here and image as example. There are duplicates of rows that give wrong totals.

Please I will appreciate a solution for my problem.

Thanks

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jithu Thomas2y ago

Accepted answer

To resolve the issue of duplicate rows and incorrect totals in your results, you need to make sure that the join between the Stock and Sales tables is correct and that you are aggregating the data appropriately. Here’s a revised version of your stored procedure that uses a proper join and aggregates the sales quantities:

please update the SQL Procedure as below: -

CREATE PROCEDURE [dbo].[AvailableQty]
    @code int = 0
AS
BEGIN
    -- Aggregate sales quantities
    SELECT 
        s.Category, 
        s.ProductName, 
        s.Model, 
        s.StockQty, 
        ISNULL(SUM(sa.SaleQty), 0) AS SaleQty
    FROM 
        Stock s
    LEFT JOIN 
        Sales sa ON s.Code = sa.Code
    WHERE 
        s.Code = @code
    GROUP BY 
        s.Category, s.ProductName, s.Model, s.StockQty
END

Riddhi Valecha2y ago
Could you share the table structure and few records so it may help in making query.