Get total count in addition of count if user voted

Question

Got three tables I'm joining. submissions, submissions_votes, and users.

I want to find out how many total helpfulVotes there are (which is a sum of the count of all submissions_votes) and I've got that.

I also want to return a count (boolean, rather) of 0 or 1 if the user_id of sv.user_id relates to the submission being viewed. The user_id is passed in to the WHERE clause.

    SELECT s.*,
      u.username,
      u.photo as userPhoto,
      COALESCE(SUM(sv.up), 0) helpfulVotes
    FROM
      submissions s
    LEFT JOIN submissions_votes sv on s.id = sv.submission_id WHERE u.id = ?
    INNER JOIN users u
    ON s.user_id = u.id

I know I need an additional join (on sv.user_id = u.id) but what would I select? Then would I group by sv.id?

Edit:

users table:

+----------------+------------------------+------+-----+-------------------+-----------------------------+
| Field          | Type                   | Null | Key | Default           | Extra                       |
+----------------+------------------------+------+-----+-------------------+-----------------------------+
| id             | int(10) unsigned       | NO   | PRI | NULL              | auto_increment              |
| email          | varchar(128)           | NO   | MUL | NULL              |                             |
| username       | varchar(23)            | NO   |     | NULL              |                             |
| type           | enum('normal','admin') | NO   |     | normal            |                             |
| about          | varchar(255)           | NO   |     | NULL              |                             |
| photo          | varchar(32)            | NO   |     | NULL              |                             |
+----------------+------------------------+------+-----+-------------------+-----------------------------+

submissions_votes table:

+---------------+---------------------+------+-----+---------+----------------+
| Field         | Type                | Null | Key | Default | Extra          |
+---------------+---------------------+------+-----+---------+----------------+
| id            | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| submission_id | int(10) unsigned    | NO   | MUL | NULL    |                |
| when          | datetime            | NO   |     | NULL    |                |
| user_id       | int(10) unsigned    | NO   | MUL | NULL    |                |
| up            | tinyint(3) unsigned | NO   |     | NULL    |                |
| down          | tinyint(3) unsigned | NO   |     | NULL    |                |
+---------------+---------------------+------+-----+---------+----------------+

submissions table:

+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
| Field       | Type                                                                                                                                                                                                                                                                                | Null | Key | Default | Extra          |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+
| id          | int(10) unsigned                                                                                                                                                                                                                                                                    | NO   | PRI | NULL    | auto_increment |
| title       | varchar(255)                                                                                                                                                                                                                                                                        | NO   | MUL | NULL    |                |
| slug        | varchar(255)                                                                                                                                                                                                                                                                        | NO   |     | NULL    |                |
| description | mediumtext                                                                                                                                                                                                                                                                          | NO   |     | NULL    |                |
| user_id     | int(11)                                                                                                                                                                                                                                                                             | NO   | MUL | NULL    |                |
| created     | datetime                                                                                                                                                                                                                                                                            | NO   |     | NULL    |                |
| type        | enum('tip','request')                                                                                                                                                                                                                                                               | NO   |     | NULL    |                |
| thumbnail   | varchar(64)                                                                                                                                                                                                                                                                         | YES  |     | NULL    |                |
| removed     | tinyint(1) unsigned                                                                                                                                                                                                                                                                 | NO   |     | 0       |                |
| keywords    | varchar(255)                                                                                                                                                                                                                                                                        | NO   |     | NULL    |                |
| ip          | int(10) unsigned                                                                                                                                                                                                                                                                    | NO   |     | NULL    |                |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+------+-----+---------+----------------+

Answer 1

You can check if the sv.user_id = input user_id using CASE and SUM it up (grouped by each submission). If the SUM is 1, then the input user_id has a submission, otherwise not. So, your input user_id would go into the CASE function.

Also, the COALESCE(SUM(sv.up), 0) requires a grouping by whichever columns of submissions and users tables are selected.

The following is the query based on the tables in the SQL Fiddle here.

SELECT 
  s.id as submission_id,
  s.title as submission_title,
  MAX(u.email) as submission_user_email,
  COALESCE(SUM(sv.up), 0) helpfulVotes,
  SUM(CASE sv.user_id
      WHEN ? THEN 1
      ELSE 0
      END) User_Submission
FROM
  submissions s
LEFT JOIN submissions_votes sv on s.id = sv.submission_id
INNER JOIN USERS u
ON s.user_id = u.id
GROUP BY s.id, s.title;

(If more columns from the submissions table need to be selected, then they need to be either grouped or aggregated)