Select Mysql Query

Question

Select Mysql Query

Navigation

#1 by (1 votes)

0

Good morning everyone before anything!

Today I have a question like every day.

I have this table in JavaFX as you can see I have 2 columns Placa y Km Sobrepasado So what I need to do here is load the data Plate and KmSobrepoasado from mu control table in mysql what happens is that the field Km exceeded is a sum of all the kilometers that have been entered in the column kmrecoaux: this is the column. To obtain that utlizo sum (kmrecoaux) as SumKmTotal

Here I show you the query you try to make but I get an error:

SELECT placa, kmrecoaux FROM control where sum(kmrecoaux) >= 5000

Ok now they will say But what do you want to do with that data and that table! Answer: I need to show all the plates of the vehicles that the sum of their kmrecoaux is greater than or equal to 5000. which means that it needs maintenance. But since the sum is a column that is created by an "ace" I do not know how to perform the query.

mysql javafx

asked by Riddick 07.11.2017 в 18:55

source

1 answer

Problems with the routing file Insert image in select option

score 1 · Accepted Answer

Mysql is the only relational database engine (that I know of) that allows you to use aggregation functions together with other columns, and not have an% explicit GROUP BY . But it is advisable that you use standard SQL, and do not depend on these bad practices.

That said, when you want to limit the value of an aggregation function (as in this case, the sum of kilometers), what you should use is HAVING (after GROUP BY ).

In this way, the query you need is the following:

SELECT placa, SUM(kmrecoaux) as Km_Sobrepasado
FROM control
GROUP BY placa
HAVING SUM(kmrecoaux) >= 5000
;