Hiya,

I need to learn the SQL query that counts all the points from more than one column and orders the matching rows based on most points in descending order.

EXAMPLE 1:
I do a keyword search for "mobile phone tutorial apps". Note 4 words.
Sql should find all the rows that contain any of these 4 keywords.
That means, since I searched for 4 keywords, sql should even pull-up those rows that just have even 1 or 2 or 3 of these 4 keywords in them.

Say, my mysql table looks like this:

id | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point

0 | mobile | 3 | phone | 3 | apps | 2 | tutorial | 2

1 | mobile | 1 | phone | 1 | apps | 3 | tutorial | 3

2 | tutorial | 3 | apps | 3 | mobile | 2 | phone | 3

3 | mobile | 5 | tutorial | 5 | apps | 5 | usa | 5

NOTE: All 4 keywords exists on the first 3 matching rows. However, only 3 words exist in the final matching row.
And the final matching row's keywords are not in the order of my keyword search.
Here in Q1, this should not matter. Sql should ignore in which order the keywords are in each column when comparing the order of my searched keywords. All it should do, is:

A). Find matching rows, regardless of how many of my searched keywords exist on each row;
B). Count the totals of each points, (count more than one column in this case), in each row; And
C) List the rows in the point's descending order.

As you can see, from my example, the following are the keyword points of each row after calculating ALL their keyword points (all point columns):

id 0 = 10 point
id 1 = 8 point
id 2 = 11 point
id 3 = 20 point

So, in this case, the SQL query should present rows in this descending order:
id 3 = 20
id 2 = 11 points
id 0 = 10 points
id 1 = 8 points
So, in this case, the SQL query should present rows in this descending order:

id | kw1 | kw1_point | kw2 | kw2_point | kw3 | kw3_point | kw4 | kw4_point

3 | mobile | 5 | tutorial | 5 | apps | 5 | usa | 5

2 | tutorial | 3 | apps | 3 | mobile | 2 | phone | 3

0 | mobile | 3 | phone | 3 | apps | 2 | tutorial | 2

1 | mobile | 1 | phone | 1 | apps | 3 | tutorial | 3

Had there been only a single "keyword point" (kw1_point) to calculate, then I would have written the SQL like this using prepared statements:

$sql = "SELECT * from keywords WHERE kw1 = ? OR kw2 = ? OR kw3 = ? OR kw4 = ? order by kw1_point desc LIMIT 100";

kw stands for "keyword_1". So, it is a column.

"kw_1_point" is another column.

"kw_2" is another column.

"kw_2_point" is another column.

"kw_3" is another column.

"kw_3_point" is another column.

"kw_4" is another column.

"kw_4_point" is another column.

    You've got a really awkward table design there: if you find yourself with column names like thing1, thing2, thing3, thing4 (and why not thing5 while we're at it?) then that's a big red flag that you're doing something wrong. What that is I don't know because I don't know what the purpose of what you have is, but a rough suspicion is that there ought to be a table with three-five columns (id, keyword point, maybe one for whatever that 1,2,3,4 represents, and maybe one to link all the keyword/point rows to a single row in whatever table these are from).

    But, more immediately, you can have expressions in ORDER BY clauses. E.g. ORDER BY kw1_point + kw2_point DESC.

    You should also look ahead and find out what will be in Q2.

    For other people, see also
    https://www.daniweb.com/programming/web-development/threads/539998/how-to-order-by-adding-total-of-all-columns-in-sql
    Which also has Q2.

      Write a Reply...