CODEWITHSUNDEEP
phpmysql
Mike
Mike

Reputation: 29

Query with sum and count in 2 tables

I use 2 tables for my php scripts.

define("EXTRA_POINTS_VALUE",15);

table "points":
id_user | point
   1    |  500
   2    |  650
   3    |  400

table "points_extra":
id_point_extra | id_user
   1           |   1
   2           |   1
   3           |   1
   4           |   2

i have need of SUM field "points.point" with COUNT(id_extra_point) * EXTRA_POINTS_VALUE in one query. It's possible?

The result must be as this:

id_user | total_point
   1    |     545     (500 + (3 count * 15))
   2    |     665     (650 + (1 count * 15))
   3    |     400     (400 + (0 count * 15))

Upvotes: 1

Views: 61

Answers (1)

John Woo
John Woo

Reputation: 263723

SELECT  a.id_user,
        a.point + (COUNT(b.id_point_extra) * ?) totalPoints
FROM    points a
        LEFT JOIN points_extra b
            ON a.id_user = b.id_user
GROUP   BY a.id_user

Upvotes: 1

Related Questions