CODEWITHSUNDEEP
phpmysqlsqlgroup-bysql-order-by
Davide
Davide

Reputation: 577

Query group by and order by

From this query:

SELECT 
  v.idutente AS idutente, 
  vl.idvideo AS idvideo, 
  (vl.likeY-vl.unlikeY) AS sott 
FROM video_likeunlike AS vl 
LEFT OUTER JOIN video AS v 
ON vl.idvideo = v.ID 
WHERE status = '1'

I have this data from table

idutente   idvideo   likeY      unlikeY    sott 
------------------------------------------------
1             70       2          5         -3
2             81       6          10        -4
1             52      200         198        2
1             54       30         2         28
2             67       20         5         15
3             22       12         0         12

I need this result, is possible?

idutente   likeY      unlikeY    sott 
------------------------------------------------
1           232         205        27
2           26          15         11
3           12           0         12

I try with this query

SELECT 
  v.idutente AS idutente, 
  vl.idvideo AS idvideo, 
  vl.likeY as likeY, 
  vl.unlikeY as unlikeY, 
  (vl.likeY-vl.unlikeY) AS sott 
FROM video_likeunlike AS vl 
LEFT OUTER JOIN video AS v 
ON vl.idvideo = v.ID 
WHERE status = '1' 
GROUP BY v.idutente 
ORDER BY sott DESC

Upvotes: 0

Views: 68

Answers (4)

Akshai
Akshai

Reputation: 229

Please use the aggregate SUM() operation for the select columns. The query can be modified as:

SELECT v.idutente AS idutente,
       vl.idvideo AS idvideo,
       SUM(vl.likeY) AS likeY,
       SUM(vl.unlikeY) AS unlikeY,
       SUM(vl.likeY-vl.unlikeY) AS sott
FROM video_likeunlike AS vl
LEFT OUTER JOIN video AS v ON vl.idvideo = v.ID
WHERE status = '1'
GROUP BY v.idutente
ORDER BY sott DESC

Upvotes: 3

Ullas
Ullas

Reputation: 11556

Use your first query as a sub-query and do the group by.

Query

SELECT t.idutente, SUM(t.likeY) as likeY, SUM(t.unlikeY) as unlikeY from(
    SELECT 
      v.idutente AS idutente, 
      vl.idvideo AS idvideo, 
      vl.likeY,
      vl.unlikeY,
      (vl.likeY-vl.unlikeY) AS sott 
    FROM video_likeunlike AS vl 
    LEFT OUTER JOIN video AS v 
    ON vl.idvideo = v.ID 
    WHERE status = '1'
) t
GROUP BY t.idutente
ORDER t.sott desc;

Upvotes: 0

wast
wast

Reputation: 996

SELECT idutente, 
  SUM(likeY) likeY, 
  SUM(unlikeY) unlikeY, 
  SUM(sott) sott
FROM mytable 
GROUP BY idutente
ORDER BY sott DESC

Try it in SQL Fiddle.

Upvotes: 1

Xyz
Xyz

Reputation: 6013

Looks like you want to use the sql sum() function

SELECT 
  v.idutente AS idutente, 
  sum(vl.likeY) as likeY, 
  sum(vl.unlikeY) as unlikeY, 
  sum(vl.likeY)-sum(vl.unlikeY) AS sott 
FROM video_likeunlike AS vl 
LEFT OUTER JOIN video AS v 
ON vl.idvideo = v.ID 
WHERE status = '1' 
GROUP BY v.idutente 
ORDER BY sott DESC

Upvotes: 1

Related Questions