-1

I have 2 tables (load+road) and I want to make a new view with creating new column (flag) that indexate the COUNT of the lines, and then GROUP BY this new index. I have tried this: (but it doesnt work)

sprintf(my_cmd, 
"CREATE VIEW myVIEW(id, Rlength, Llength, flag) AS "
"SELECT road.id, road.length, load.length, COUNT(*) AS flag 
FROM road, load "
"WHERE road.id=load.id; "
"SELECT id, Rlength, Llength 
FROM myVIEW"
"GROUP BY flag");

ERROR: Error executing query: ERROR: column "road.id" must appear in the GROUP BY clause or be used in an aggregate function

I am using MY SQL.

*edit:

I dont want that the new column (flag) appears in the last SELECT, but I want to group by it.. dont know if it can be done. if not, the thing I wanna reach, is to use group by on "SELECT id, Rlength, Llength " and to get all the lines in an only one group, but I dont have a Common parameter between thees lines so I have trying to add this "flag"

the full code (sorry for long question):

sprintf(my_cmd, 
"CREATE VIEW myVIEW3(id, Rlength, Llength, flag) AS"
" SELECT road.id, road.length, load.length, COUNT(*) AS flag 
FROM road, load"
" WHERE road.id=load.id;"
" SELECT id, Rlength, Llength FROM myVIEW3"
" GROUP BY flag"
" HAVING COUNT(*) <= %d"
" ORDER BY (CAST(Llength AS float) / CAST(Rlength AS float)) DESC, id DESC",k);

and what I am trying to do, is to get first k lines after making some ORDER without using LIMIT/TOP (its an assigment). So I have tried using new VIEW with some indecator that I will use for grouping all lines into one group and then use HAVING COUNT(flag) <= k.

road:

.--------.----------------.----------------. | Id | length | speed | .--------.----------------.----------------.-

| 9 | 55 | 90 |

| 10 | 44 | 80 |

| 11 | 70 | 100 |

load:

.--------.----------------.----------------. | Id | length | speed | .--------.----------------.----------------.-

| 9 | 10 | 20 |

| 10 | 15 | 30 |

| 11 | 30 | 60 |

COMMAND: loadRanking 2 (k=2, so I want to get first 2 lines after some ORDER, lets not talk about the ORDER in this result)

result:

.--------.----------------.----------------. | Id | length | speed | .--------.----------------.----------------.-

| 9 | 10/55 | 20/90 |

| 10 | 15/44 | 30/80 |

Atheel Massalha
  • 483
  • 1
  • 4
  • 10

1 Answers1

1

Your group by should contain all columns that are being selected that are not part of the aggregate function. So your GROUP BY should look like this:

GROUP BY road.id, road.length, load.length

That being said, I am quite confused by why you have two queries here. I suspect your query should look something like this:

SELECT road.id, road.length, load.length, COUNT(*) AS flag 
FROM road, load 
WHERE road.id=load.id
GROUP BY road.id, road.length, load.length
HAVING COUNT(*) <= %d
 ORDER BY (CAST(load.length AS float) / CAST(road.length AS float)) DESC, road.id DESC

The GROUP BY Statement

Additional note: Try making sure your query works before making it into a view.

Jimeh
  • 367
  • 1
  • 6
  • 16
  • Oh you want the min and max per id? Let me see what I can figure out for you. – Jimeh May 14 '15 at 03:43
  • it is not about the id... I just want to get first k lines without using LIMIT – Atheel Massalha May 14 '15 at 03:51
  • I don't have enough rep to respond to your original post. I still don't quite understand what you want. Where is speed coming from? Are you just looking to combine the two tables? Why wasn't id of 11 included in the result? I think if you posted the actual assignment question, I could guide you in the right direction. – Jimeh May 14 '15 at 03:55
  • Is it possible the question wants you to use PHP to limit the results? Something like this? [http://stackoverflow.com/a/17070820/3843101](http://stackoverflow.com/a/17070820/3843101) – Jimeh May 14 '15 at 04:07
  • I want to limit without using LIMIT , yes. but what is PHP ? I am using MY SQL in c . – Atheel Massalha May 14 '15 at 04:48
  • Ah sorry, I just googled sprintf() ant noticed it was a PHP function and found a similar request on stackoverflow. – Jimeh May 14 '15 at 19:40
  • The only other thing I can think of how to do this is something like [http://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select](http://stackoverflow.com/questions/2520357/mysql-get-row-number-on-select). – Jimeh May 15 '15 at 01:59