Showing entries 1 to 3
Displaying posts with tag: sum (reset)
Advanced JSON for MySQL: indexing and aggregation for highly complex JSON documents

What is JSON
JSON is an text based, human readable format for transmitting data between systems, for serializing objects and for storing document store data for documents that have different attributes/schema for each document. Popular document store databases use JSON (and the related BSON) for storing and transmitting data.

Problems with JSON in MySQL
It is difficult to inter-operate between MySQL and MongoDB (or other document databases) because JSON has traditionally been very difficult to work with. Up until recently, JSON is just a TEXT document. I said up until recently, so what has changed? The biggest thing is that there are new JSON UDF by Sveta Smirnova, which are part of the MySQL 5.7 Labs releases. Currently the JSON UDF are up to version 0.0.4. While these new UDF are a welcome edition to the MySQL database, they don't solve the really tough …

[Read more]
Advanced JSON for MySQL: indexing and aggregation for highly complex JSON documents

What is JSON
JSON is an text based, human readable format for transmitting data between systems, for serializing objects and for storing document store data for documents that have different attributes/schema for each document. Popular document store databases use JSON (and the related BSON) for storing and transmitting data.

Problems with JSON in MySQL
It is difficult to inter-operate between MySQL and MongoDB (or other document databases) because JSON has traditionally been very difficult to work with. Up until recently, JSON is just a TEXT document. I said up until recently, so what has changed? The biggest thing is that there are new JSON UDF by Sveta Smirnova, which are part of the MySQL 5.7 Labs releases. Currently the JSON UDF are up to version 0.0.4. While these new UDF are a welcome edition to the MySQL database, they don't solve the really tough …

[Read more]
SUM for the 5 best models in 5 best brands

As we can see in the solution:

Expression are evaluated in the column order in the select clause.

This is the same for the where clause.  An advice is to test first the expression that get more chance to return false at the head of the where conditions :   



  1. SET @BRAND=0;
  2. SET @ct=0;
  3. SELECT
  4.  *
  5. FROM
  6.  (SELECT
  7.    IF(@BRAND<>id_BRAND,@ct:=0 ,@ct:=@ct ) ,
  8.    @ct:=@ct+1 AS ct,   
  9.    @BRAND:=id_BRAND,
  10.    t2.*
  11.   FROM
  12.    (SELECT
  13.      st.ID_BRAND,
  14.      st.ID_MODEL,
  15.      SUM(COMPTE) AS total,
  16.      AVG(totalm)
  17.   …
[Read more]
Showing entries 1 to 3