StudentDev StudentDev - 3 years ago 168
SQL Question

c# chart sales by percent. winforms

Hello i want to make a chart that gets the Quantity of the product and the sales of the product and shows the percent of sales in the chart if possible.

For example

ProductA
Qty: 100,
ProductA
sales:50. so its 50% sales.

I have this working chart now that sums all of the sales of the product but it does not show the quantity. (the quantity is from a different table called Products)

And if i get those to work i want to for example, show in chart products that are only above 50% sales or product that are only below 50% sales.

Any help is appreciated. Thank you in advance.

private void loadchartFastt()
{
chart1.Series[0].Points.Clear();
chart1.ChartAreas["ChartArea1"].AxisX.Interval = 1;
using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["cnn"].ConnectionString))
{
if (cnn.State == ConnectionState.Closed)
cnn.Open();
SqlCommand command = new SqlCommand("SELECT TOP 5 ProductName, Sum(QtySold) as QtySold FROM Sales_productholder group by ProductName order by SUM(QtySold) desc", cnn); //top selling with desc

SqlDataReader read = command.ExecuteReader();

while (read.Read())
{
this.chart1.Series["Pieces Sold"].Points.AddXY(read["ProductName"], read["QtySold"]);

}
read.Close();

}
}


Update pic base on sir Jeric's answer.
enter image description here

Answer Source

In order to achieved that you can try this:

  1. Join the two table based on the relationship of 'ProductID'
  2. perform an expression column that will get the sales percentage by doing [Total Sold Quantity]/[Quantity] * 100.

Note: We must convert sp.QtySold and Quantity to float in order to get the result with decimal point. Then you can enclose the expression to DECIMAL to only show two decimal points.

  1. Then add another series to show sales percentage.

Below is the sample query I made based on your scenario: enter image description here

Try this code below:

var sql = @"SELECT TOP 5 
                sp.ProductName, 
                SUM(sp.QtySold) AS QtySold,
                p.Quantity,
                CAST((CAST(SUM(sp.QtySold) AS FLOAT) / CAST(p.Quantity AS FLOAT)) * 100 AS DECIMAL(8,2)) [SalesPercentage]
            FROM 
                Sales_productholder sp
                JOIN Products p ON (sp.ProductID = p.ProductID)
            GROUP BY 
                sp.ProductName, p.ProductID, p.Quantity
            ORDER BY 
                SUM(sp.QtySold) DESC";
SqlCommand command = new SqlCommand(sql, cnn); //top selling with desc
SqlDataReader read = command.ExecuteReader();
while (read.Read())
{
    this.chart1.Series["Pieces Sold"].Points.AddXY(read["ProductName"], read["QtySold"]);
    //add another series for the sold %
    this.chart1.Series["Sold Percentage"].Points.AddXY(read["ProductName"], read["SalesPercentage"]);
}

Result: enter image description here

You can try to download this source code for reference: https://github.com/makubex88/SampleChartWinform

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download