Home News Contact Us Forum About Us Demos Products F.A.Q.
Shopping Cart
You currently have 0 items in your cart.


Recent Events
  • 18/11/2017 Black Friday is coming

    Don't wait BLACK FRIDAY, use FRIDAY17 coupon code to get 35% discount for all our extensions right now.

  • 05/09/2017 BIG Autumn SALE

    Use AUTUMN17 coupon code to buy our WordPress or Joomla! extensions with 25% discount. Hurry up! The offer is valid till 11 September.


2Checkout.com, Inc. is an authorized retailer of goods and services provided by ARI Soft. 2CheckOut




Follow us on twitter



Welcome, Guest
Please Login or Register.    Lost Password?

[SOLVED] SET in SQL query doesn't work
(1 viewing) (1) Guest
Go to bottomPage: 1
TOPIC: [SOLVED] SET in SQL query doesn't work
#58461
[SOLVED] SET in SQL query doesn't work 1 Year, 3 Months ago Karma: 0
I have the following query that creates the two columns that I need for the chart. However, the addon shows only "No data available.". I believe it is because of the "SET" part of the query. Unfortunately, MySQL doesn't allow SET to be a part of a view, and I don't know how to use procedures.
The math operation is necessary to show the total number of people (q1.1 are those who left that month)
Please tell me how to fix it.

Code:

SET @runtot:=0;
SELECT
   
   CONCAT(q1.d,'-',LPAD(q1.m,2,0)) AS Periodo,
   
   (@runtot := @runtot + q1.c - q1.l) AS `N. dipendenti`
FROM
   (SELECT
       YEAR(calendar.datefield) AS d,
       month(calendar.datefield) AS m,
       SUM(statistica_assunti_n.assunzioni) AS c,
   SUM(statistica_licenziati_n.licenziamenti) AS l
    FROM  calendar
INNER JOIN statistica_assunti_n
    ON calendar.datefield = statistica_assunti_n.data_assunzioni
  INNER JOIN statistica_licenziati_n
    ON calendar.datefield = statistica_licenziati_n.data_licenziamenti
WHERE YEAR(datefield) BETWEEN '2011' AND YEAR(NOW()) AND datefield <= DATE(NOW())
    GROUP  BY d, m
    ORDER  BY d, m) AS q1

Last Edit: 2016/09/09 08:16 By vladimir84.
The administrator has disabled public write access.
 
#58463
Re:SET in SQL query doesn't work 1 Year, 3 Months ago Karma: 670
Hello,

A variable can be defined in "SELECT" SQL query like in this example.

Regards,
ARI Soft
The administrator has disabled public write access.
 
#58464
Re:SET in SQL query doesn't work 1 Year, 3 Months ago Karma: 0
thank you, that's it!!!
I've been looking at stackoverflow for hours and hours and couldn't find it.
The administrator has disabled public write access.
 
#58465
Re:SET in SQL query doesn't work 1 Year, 3 Months ago Karma: 670
You are welcome. Feel free to contact, together we can find an answer more faster

Regards,
ARI Soft
The administrator has disabled public write access.
 
Go to topPage: 1