Conversion and change in QTableView + SQL
-
Hello! Really I need your advice.
There are database, where in one column in the form of written codes: 10, 10-09, 10-10, 10-11, 15, 15-02, 15-03, and so on. Each value corresponds to month (in numerical format). I do a SQL query, which displays what codes in which month, with the calculation of how many times were called. The request appears in QTableView.
In this way: HOW MANY TIMES HAS BEEN CALLED, IN WHAT MONTH, WHAT CODE.
For example, the output will be:
125, 04, 10-09
4, 04, 10-10
93, 05, 15-02
43, 05, 15-03What I want:
All codes that start with the same two numbers must be united in a single line, and only those already displayed by the first two digits. But I want to save sort by month in order to the codes of the different months are not stuck together. Naturally, the first column (the number of times was called) for concatenating should summarize the respective values of the rows that are glued together.
As a result, should leave as follows:
129, 04, 10
136, 05, 15Can you solve this issue purely SQL? Or you need to use QT model?
Please help, just can not imagine how to do it. -
Hello! Really I need your advice.
There are database, where in one column in the form of written codes: 10, 10-09, 10-10, 10-11, 15, 15-02, 15-03, and so on. Each value corresponds to month (in numerical format). I do a SQL query, which displays what codes in which month, with the calculation of how many times were called. The request appears in QTableView.
In this way: HOW MANY TIMES HAS BEEN CALLED, IN WHAT MONTH, WHAT CODE.
For example, the output will be:
125, 04, 10-09
4, 04, 10-10
93, 05, 15-02
43, 05, 15-03What I want:
All codes that start with the same two numbers must be united in a single line, and only those already displayed by the first two digits. But I want to save sort by month in order to the codes of the different months are not stuck together. Naturally, the first column (the number of times was called) for concatenating should summarize the respective values of the rows that are glued together.
As a result, should leave as follows:
129, 04, 10
136, 05, 15Can you solve this issue purely SQL? Or you need to use QT model?
Please help, just can not imagine how to do it.@derilshows Does someone know how to do that?
-
I found the solution, but only by SQL query.
-
Hi,
Can you share that solution for other to benefit ?
Why would it be a problem doing it only by SQLqueries ?
-
Hi,
Can you share that solution for other to benefit ?
Why would it be a problem doing it only by SQLqueries ?
@SGaist sure. The structure is:
HOW MANY TIMES HAS BEEN CALLED, IN WHAT MONTH, WHAT CODE.
COUNT(*), MONTHZ, CODE
That code is the answer:SELECT sum(cnt), monthz, code FROM (SELECT COUNT(*) AS cnt, monthz, LEFT(code, 2) AS code FROM tab GROUP BY monthz, code ) AS src;I couldn't do that, cause I did SUM(COUNT(*)), but it's incorrect.
But we can make query from the result of other query! And it works.
sum(cnt) makes sum of values.
and group makes one line
