Sum the column data based on the from month and to month selection

[Senthilkumar]

Dear Team,

I am having two inputs (From month & To mont).  I want to sum the column data basedon the month selection. My sql table looks like 

my code is 

 $dealerid = $_POST["dealerid"];
 $FromMonth=$_POST["fromMonth"];
 $ToMonth = $_POST["toMonth"];
 $ES_Model = $_POST["ES_Model"];

$countMonth =   //here the month sould update like Jan+Feb+Mar... as per the selection

 if($FromMonth == "" && $ToMonth == ""){
     $Month_Condition = "";
     $Sum = "sum(total)";
 }else{
     $Month_Condition = "AND Month >= '$FromMonth' AND Month <= '$ToMonth'";
     $Sum = "sum($countMonth)";
 }

if ($ES_Model == 'All') {
    $Modal_Cond = '';
} else {
    $Modal_Cond = "And Model ='$ES_Model'";
}


$query = "SELECT category, $Sum as target FROM currentyeardata WHERE 1 $Modal_Cond GROUP BY category"
$result = mysqli_query($conn, $query);

I was struckup on $countMonth

When i am selecting the from month (2023-01) and to month (2023-03) , it should sum the column Jan+Feb+Mar where the year is 2023. When i am selecting from month (2023-05) and to month (2023-09) , it should sum the column May+Jun+Jul+Aug+Sep where the year is 2023 and When i am selecting from month (2023-11) and to month (2024-02) , it should sum the column Nov+Dec (2023) & Jan+Feb(2024) .

I want to output like bellow

Can any one help me how to do this

Edited September 7, 2023 by Senthilkumar

[ginerjm]

I don't see how your math works for your 2nd and 3rd example.    23-05 to 23-05 should sum one month only.  And 23-01 to 24-02 should sum all of 23 plus the first 2 months of 24.

I am also assuming that your use of the word 'two' s/b really 'to'. 

[Senthilkumar]

correction

When i am selecting the from month (2023-01) and to month (2023-03) , it should sum the column Jan+Feb+Mar where the year is 2023. When i am selecting from month (2023-05) and to month (2023-09) , it should sum the column May+Jun+Jul+Aug+Sep where the year is 2023 and When i am selecting from month (2023-11) and to month (2024-02) , it should sum the column Nov+Dec (2023) & Jan+Feb(2024) .

[ginerjm]

That is a whole new post.

So - what is the coding issue now?

PS - you should never include a "total" amount on a record that contains all the details that comprise that total.  It should always be generated by a query.

[mac_gyver]

if you had properly stored your data as has been previously suggested in your threads, with date and amount columns and a row for each piece of data, this would be a simple task that could be accomplished in a straight-forward sql query. but because you have a year column and separate month name columns, your code must take the year and month from the 'from' and 'to' values and figure out which rows and which columns to use to calculate the result.

[Barand]

(Illustrating what mac_gyver has just told you.)

What you have shown us is a spreadsheet table. Relational database tables are not spreadsheets - each row should contain a single value plus identifying attributes.

Your spreadsheet contains several rows for each category/year. Why? What is distinctive about each row?

Whatever it is, I have named the column "rowid?"

+----------+--------+--------+--------+
| category | rowid? | date   | amount |
+----------+--------+--------+--------+
| user1    |    1   | 202301 |    1   |
| user1    |    1   | 202302 |    2   |
| user1    |    1   | 202303 |    2   |
| user1    |    1   | 202304 |    1   |
| user1    |    1   | 202305 |    2   |
+----------+--------+--------+--------+

Then your query becomes

SELECT category
     , sum(amount) as total
FROM currentyeardata
WHERE date BETWEEN '2023-01' AND '20203-05'
GROUP BY category

 

 

[Senthilkumar]

Dear Barand,

That is not spreadsheets. This is the sample of my database. My database is 

[ginerjm]

He is telling you that you have CREATED a database that is more of a SPREADSHEET THAN A DATABASE.  You are not doing is correctly.

[Senthilkumar]

can you pls guide me how to do this

[ginerjm]

You need to do some reading up on RDBMS (google it) in order to learn that.  Stop thinking about spreadsheets and think more about what your data is and how you should keep track of it.  Someone here might offer some tips but I'll leave that kind of help to others.

[Barand]

I know it's your database table. But you have designed it like a spreadsheet, which is completely the wrong approach.

Data stored in a database needs to be correctly normalized (There's a link in my sig to a tutorial). For example,

• Dealername should be stored once in a dealer table with its id, not repeated in every row in this table. Only ids should be stored in multiple tables.
• Repeating columns likeyour months should be in separate rows.
• If, as I am guessing, a model belongs to particular category, the that category's id should be stored  in the model table.
• It looks like there is also a dependency between dealer and category, but only you know that. If there is, then dealer id should be in the category table.
• I have no idea to which entity (dealer, category, model, year) the status belongs.
• Derived values, such as quarter and annual totals) should not be stored. Get those when needed with a query.

Your tablename suggests you have a table for the current year although the data show two years (confusing, isn't it?). If you split your data by years it make historical reports a problem.

I don't know the relationships between entities (you need to verify for youself), but the database should look something like this below, and not all stored in a single table.

example data

TABLE: target
+-----+--------+------------+--------+--------+
|  id | model  | month      | amount | status |
+-----+--------+------------+--------+--------+
| 1   | GR170  | 2023-01-00 |    1   |    1   |
| 2   | GR170  | 2023-02-00 |    2   |    1   |
| 3   | GR170  | 2023-03-00 |    2   |    1   |
| 4   | GR170  | 2023-04-00 |    1   |    1   |
| 5   | GR170  | 2023-05-00 |    2   |    1   |
+-----+--------+------------+--------+--------+