logo
down
shadow

SQL how to subtract result row 1 from row 2, row 2 from row 3


SQL how to subtract result row 1 from row 2, row 2 from row 3

Content Index :

SQL how to subtract result row 1 from row 2, row 2 from row 3
Tag : mysql , By : Reiner
Date : November 28 2020, 04:01 AM

will be helpful for those in need You probably want to look into MySQL's user defined variables, and then you probably want to do something like this:
SET @prev := NULL;
SELECT
    DATE(created_at),
    price - COALESCE(@prev, price) AS price_change,
    name,
    (@prev := price) AS price FROM (
        SELECT * FROM items ORDER BY DATE(created_at)
    ) t1
GROUP BY
    name, price, DATE(created_at)
HAVING name = 'Ibuprofen'
ORDER BY DATE(created_at);
Query OK, 0 rows affected (0.00 sec)
SET @prev := NULL;
SELECT
    DATE(created_at),
    price - COALESCE(@prev, price) AS price_change,
    name,
    (@prev := price) AS price FROM (
        SELECT * FROM items ORDER BY DATE(created_at)
    ) t1
GROUP BY
    name, price, DATE(created_at)
HAVING name = 'Ibuprofen'
ORDER BY DATE(created_at);

Query OK, 0 rows affected (0.00 sec)

+------------------+--------------+-----------+-------+
| DATE(created_at) | price_change | name      | price |
+------------------+--------------+-----------+-------+
| 2018-12-10       |            0 | Ibuprofen |   110 |
| 2018-12-13       |          -10 | Ibuprofen |   100 |
| 2018-12-13       |           20 | Ibuprofen |   120 |
+------------------+--------------+-----------+-------+

3 rows in set, 1 warning (0.00 sec)

SELECT * FROM items;
+----+-------+----------------+---------------------+
| id | price | name           | created_at          |
+----+-------+----------------+---------------------+
|  8 |   100 | Ibuprofen      | 2018-12-13 12:52:35 |
|  9 |   110 | Ibuprofen      | 2018-12-10 12:12:12 |
| 10 |   120 | Ibuprofen      | 2018-12-13 12:52:35 |
| 11 |  1000 | Something else | 2018-12-13 13:01:19 |
+----+-------+----------------+---------------------+

4 rows in set (0.00 sec)

Comments
No Comments Right Now !

Boards Message :
You Must Login Or Sign Up to Add Your Comments .

Share : facebook icon twitter icon

Rails subtract result for the previous result


Tag : ruby-on-rails , By : Alex Bartzas
Date : March 29 2020, 07:55 AM
This might help you You could use each_with_index()
- patient_weights = @patient.weight
- patient_weights.each_with_index do |m, i|
  tr
    td  = m.date
    td  = m.weight
    td  = (m.weight / (@patient.weight.first.inch**2).to_f).round(2)
    td  = (i > 0) ? (m.weight - patient_weights[i-1].weight) : "-"

SUM and Subtract result depending by column result in Mysql query


Tag : mysql , By : Jason Haar
Date : March 29 2020, 07:55 AM
Does that help I have the following query: , You can use conditional aggregates:
SELECT SUM(CASE WHEN cashtransactionstatus_id IN (1,3) THEN cashtransaction_amount END) -
       SUM(CASE WHEN cashtransactionstatus_id IN (2,4,5) THEN cashtransaction_amount END) AS endtotal
FROM cash_transactions 
WHERE cashpaymenttype_id = 1

SUBTRACT two columns SUM, then SUBTRACT the result and SUM the result to show minimum quantity


Tag : mysql , By : ck1
Date : March 29 2020, 07:55 AM
hop of those help? Put your SUM query in a subquery, and then use COUNT(*) in the main query to get the number that match your criteria:
$sql = "SELECT COUNT(*) AS count
        FROM (SELECT SUM(existencia - vendido) AS texist, alert_cantidad 
              FROM PRODUCTOS 
              GROUP BY cod) AS x
        WHERE texist < alert_cantidad";
$result = $conn->query($sql);
$row = $result->fetch(PDO::FETCH_ASSOC);
$count2 = $row['count'];
echo "<li><a class='btn btn-info' href='alertProduct.php'>
    <i class='glyphicons glyphicons-circle_exclamation_mark'></i>
    " .$result2." minima cantidad
    </a></li>";

Subtract result of two CTE


Tag : sql-server , By : Andrew L.
Date : March 29 2020, 07:55 AM
With these it helps So I have a query like this , You can't use CTE that way. Try
;WITH totalcte
     AS (SELECT Count(*) AS TotalTargets
         FROM   targets
         WHERE  insertedbyuserid = @userId),
     filteredcte
     AS (SELECT Count(*) AS FilteredTargets
         FROM   requestdetails
         WHERE  requestid = @rId)
INSERT INTO #requesttargets
            (NAME,
             value)
SELECT 'NotInSurveillence',
       totalcte.totaltargets - filteredcte.filteredtargets
FROM   totalcte
       CROSS JOIN filteredcte  
FROM  TotalCTE CROSS JOIN FilteredCTE 
 ;WITH totalcte
     AS (SELECT Count(*) AS TotalTargets
         FROM   targets
         WHERE  insertedbyuserid = @userId),
     filteredcte
     AS (SELECT Count(*) AS FilteredTargets
         FROM   requestdetails
         WHERE  requestid = @rId)
INSERT INTO #requesttargets
            (NAME,
             value)
SELECT 'NotInSurveillence',
       totalcte.totaltargets - filteredcte.filteredtargets
FROM   totalcte
       CROSS JOIN filteredcte
UNION ALL
SELECT 'TotalTargets',
       totalcte.totaltargets
FROM    totalcte
UNION ALL
SELECT 'UnderSurveillence',
       filteredcte.filteredtargets  
FROM   filteredcte

Why does pow() subtract 1 from my result?


Tag : c , By : Pepe Araya
Date : March 29 2020, 07:55 AM
Related Posts Related QUESTIONS :
  • Remove HTML tags from record
  • Alternative to Intersect in MySQL
  • Insert a bunch of duplicate rows into a mysql table
  • Explain this SQL query in plain English
  • Need an alternative to two left joins
  • Wordpress Access WP-Admin when installed in a sub directory
  • How to query fields with MySQL-Regex that contain thousand separator?
  • TomEE JPA MySql Configuration "An invalid Lifecycle transition was attempted"
  • Update a row with data from another row in the same table based on a similar field
  • SequelizeConnectionRefusedError: connect ECONNREFUSED 127.0.0.1:3306
  • Getting ER_PARSE_ERROR when using Intersect and multiple input vaules of email format
  • Laravel get many objects in one object
  • MySql: How do I sort a query by 2 specific categories and then by price?
  • Generating monthly report mysql
  • getting error in Sql Query incorrect syntax near ')'
  • MariaDB/MySQL select query replace json array of ids into concatenated values
  • How to create a MySQL Query to get sales for each date of month (Like Calendar)?
  • Insert query fails with error duplicate entry , where as select query returns null
  • MySQL - ORDER BY multiple words, then alphabetically?
  • With a one-to-many relation, how do you get every row of table A if table B doesn't reference some rows of A?
  • How to add temporary column with data with MYSQL?
  • Why is subquery join much faster than direct join
  • Under standing Update Queries, and how many sets one query can have
  • How to select multiple values in a subquery but have each value assigned to matching parent select value?
  • SQL getting a column to match multiple conditions in CASE
  • Converting date operation from MySQL to PostgreSQL
  • MySQL - Join these subqueries into a single query
  • Show the list of all product's names ordered along with first and last names of the customers
  • Mysql `OR` condition doesn't work with JOIN
  • how can I hide the data that my controller returns making a CRUD in Laravel & vue.js?
  • Sum of values for all accounts using the most recent date for each
  • MySQL Query between three tables (with also ORDER and LIMIT)
  • MySQL date_format () function vs SQL Like operator
  • How to Combine two select and two where clause?
  • How to convert this (strange formated) data ? a:5:{s:15:"nc_notification";s:1:"2";s:20:"iso_add
  • SELECT result within select isn't reachable?
  • How to call stored procedure at sqlfiddle?
  • How Do I convert this table from row to column?
  • Mysql check if table has any value
  • Mysql Index misbehave in rails
  • Calculate number of students per class without using a JOIN
  • "new ephemeral certificate expires too soon: current time:"M
  • How to write this SQL (about date and time)?
  • What would take longer to execute in a million row table LIMIT 37,22 or LIMIT 100037,22
  • Can't connect a spring-boot 2 service to mysql in a different container
  • Insert statement using variable in sql query
  • Getting all users except admins in many-to-many relationship
  • How to pass the data from modal to MYSQL using VueJS
  • MYSQL - get a row from a table in every database
  • date type casting in npm package?
  • GROUP BY a, b VS GROUP BY b, a
  • How to store multiple values on a mysql table
  • 2 SELECT in a query with ORDER BY
  • BigQuery: How to run cohort / retention analysis for userbase across 2.5 years?
  • Can't connect local database. Sequel Pro encountered an unexpected error
  • How do I change the auto increment counter in MySQL from +1 to +43
  • How to query non duplicate rows but keep the primary key
  • How to group all scores by username in a table and display rank?
  • How to remove timestamp with the date using sql query?
  • Modify SQL query to get data only from past 2 months
  • shadow
    Privacy Policy - Terms - Contact Us © scrbit.com