Mysql: Get Total in Last Row of MySQL Result

MySQL: Get total in last row of MySql result

(SELECT product, 
        quantity, 
        something 
 FROM   tablename) 
UNION 
(SELECT "all"          AS product, 
        SUM(quantity)  AS quantity, 
        SUM(something) AS something 
 FROM   tablename) 

This is working query. It will add a fourth row as desired at the end of your result

How to display the total count of the last row by filestatus?

Use a correlated subquery:

SELECT COUNT(DISTINCT f_bankid) AS tcount
FROM tbl_fileStatus t
WHERE ? = (SELECT f_filestatus FROM tbl_fileStatus WHERE f_bankid = t.f_bankid ORDER BY f_id DESC LIMIT 1)

Replace ? with the f_bankid you search for.

See the demo.

In MySql 8.0+ you can use FIRST_VALUE() window function:

SELECT COUNT(*) AS tcount
FROM (
  SELECT DISTINCT f_bankid, 
         FIRST_VALUE(f_filestatus) OVER (PARTITION BY f_bankid ORDER BY f_id DESC) f_filestatus
  FROM tbl_fileStatus
) t
WHERE f_filestatus = ?

See the demo.

If you want results for all f_filestatus in 1 row:

SELECT
  SUM(f_filestatus = 1) AS tcount1,
  SUM(f_filestatus = 2) AS tcount2,
  SUM(f_filestatus = 3) AS tcount3
FROM (
  SELECT t.f_bankid, t.f_filestatus
  FROM tbl_fileStatus t
  WHERE t.f_id = (SELECT f_id FROM tbl_fileStatus WHERE f_bankid = t.f_bankid ORDER BY f_id DESC LIMIT 1)
) t

See the demo.

Results:

> tcount1 | tcount2 | tcount3
> ------: | ------: | ------:
>       0 |       1 |       2

How to calculate sum of each group last row in mysql

One way to do this is to take the max (for each companyAccountId) of a complex string that joins the id and the field you want to find for the highest id, then extract the field you want from the end and convert it back to a number (all in a subquery, so you can sum all the resulting values)

select sum(latestTotalVan) as totalVan, sum(latestTotalBike) as totalBike
from (
    select
        cast(substring(max(concat(lpad(id,11,'0'),totalVan)) from 12) as unsigned) latestTotalVan,
        cast(substring(max(concat(lpad(id,11,'0'),totalBike)) from 12) as unsigned) latestTotalBike
    from vehicle_assignment_history
    where date between '2021-11-11 00:00:00' and '2021-11-14 00:00:00'
    group by companyAccountId
) latest_values

fiddle

mysql 8 adds window functions that make this kind of thing much easier.

How to get total number of rows, and latest row's value in MySql?

One way to achieve this would be to use the latestOfMany method. Add the following to your Post model:

public function latestComment()
{
    return $this->hasOne(Comment::class)->latestOfMany();
}

Then you can eager load the results:

$posts = Post::with('latestComment')->withCount('comments as total_comments')->get();

How do I add a total as the last row in my sql?

Use the WITH ROLLUP modifier to GROUP BY:

SELECT   IFNULL(env, 'Unknown'),
         COUNT(*) AS Count
FROM     env_table
GROUP BY env WITH ROLLUP
ORDER BY env

How can I count the numbers of rows that a MySQL query returned?

Getting total rows in a query result...

You could just iterate the result and count them. You don't say what language or client library you are using, but the API does provide a mysql_num_rows function which can tell you the number of rows in a result.

This is exposed in PHP, for example, as the mysqli_num_rows function. As you've edited the question to mention you're using PHP, here's a simple example using mysqli functions:

$link = mysqli_connect("localhost", "user", "password", "database");

$result = mysqli_query($link, "SELECT * FROM table1");
$num_rows = mysqli_num_rows($result);

echo "$num_rows Rows\n";

Getting a count of rows matching some criteria...

Just use COUNT(*) - see Counting Rows in the MySQL manual. For example:

SELECT COUNT(*) FROM foo WHERE bar= 'value';

Get total rows when LIMIT is used...

If you'd used a LIMIT clause but want to know how many rows you'd get without it, use SQL_CALC_FOUND_ROWS in your query, followed by SELECT FOUND_ROWS();

SELECT SQL_CALC_FOUND_ROWS * FROM foo
   WHERE bar="value" 
   LIMIT 10;

SELECT FOUND_ROWS();

For very large tables, this isn't going to be particularly efficient, and you're better off running a simpler query to obtain a count and caching it before running your queries to get pages of data.

MySQL: Add a Total Count to the last row of SQL Query for Unique value only

I know it is not very efficient, but an easy solution woudl be to use this query:

    SELECT null as total,
           T5.orders_id As OrdID,
           T3.products_name As ProdName,   
           T2.products_quantity As Qty
      From /*PREFIX*/products T1
 Left Join /*PREFIX*/orders_products T2 On (T1.products_id = T2.products_id)
Inner Join /*PREFIX*/orders T5 On (T5.orders_id = T2.orders_id)
 Left Join /*PREFIX*/manufacturers T4 On (T1.manufacturers_id = T4.manufacturers_id)
     Where (T5.date_purchased >= 20120101) 
       And (T5.date_purchased <= 20131216) 
       And T5.orders_status = 'x' 
  Order By T5.orders_id
     UNION
    SELECT count(*) AS total,
           null As OrdID,
           null As ProdName,   
           null As Qty
      FROM (select T5.orders_id
              From /*PREFIX*/products T1
         Left Join /*PREFIX*/orders_products T2 On (T1.products_id = T2.products_id)
        Inner Join /*PREFIX*/orders T5 On (T5.orders_id = T2.orders_id)
         Left Join /*PREFIX*/manufacturers T4 On (T1.manufacturers_id = T4.manufacturers_id)
             Where (T5.date_purchased >= 20120101) 
               And (T5.date_purchased <= 20131216) 
               And T5.orders_status = 'x' 
          GROUP BY T5.orders_id
          ) as s

Pay attention to UNION and GROUP BY.

showing sum in last row of table using mysql

I don't know why you want that but give this a try:

SELECT Col1, Col2 FROM tableName
UNION
SELECT 'SUM' as Col1, SUM(Col2) Col2 FROM tableName

MYSQL Query to get the SUM and LAST record value in Group BY

Here's one option using a correlated subquery:

SELECT a.id, SUM(ash.amount_spend) AS spend_total,
       (SELECT amount_spend 
        FROM account_spend_history as ash2 
        WHERE ash2.account_id = a.id 
        ORDER BY ash2.id DESC LIMIT 1) as last_spend
FROM accounts as a
    INNER JOIN account_spend_history AS ash ON ash.account_id = a.id
GROUP BY a.id

Select last row and sum of column in MySQL query

You can use group_concat to accumulate the payment values in a certain order, and then use substring_index to extract the first from that:

select   id, 
         name, 
         sum(amount), 
         substring_index(group_concat(payment order by date desc), ',', 1)
from     mytable
group by id

This assumes you want to take the last payment in the order by the date column. The payment column seems to be a non-date value, which will be useless to sort by.

If you need to take the last value of payment in the order of payment date, you should define that column as a date. Then you can do a simple max:

select   id, 
         name, 
         sum(amount), 
         max(payment)
from     mytable
group by id

---

Related Topics