How to count rows from an inner joined table

Question

I am pulling all of the information for solutions using cross referenced tables.

SELECT
    s.*, u.forname, u.surname, u.email, u.tel, p.type
FROM _user_solution s
INNER JOIN _users u
    ON s.uid = u.uid
INNER JOIN _payment_plans p
    ON p.pid = s.payment_plan_type

Which works fine and my results are as expected. However, I have another table which holds tasks for that solution, each task has a progress. I want to bring out how many tasks that solution has, I have tried:

SELECT
    s.*, u.forname, u.surname, u.email, u.tel, p.type,
    (SELECT COUNT(*) FROM t WHERE t.progress < 100 AS task)
FROM _user_solution s
INNER JOIN _users u
    ON s.uid = u.uid
INNER JOIN _payment_plans p
    ON p.pid = s.payment_plan_type
INNER JOIN _solution_tasks t
    ON s.sid = t.assigned_for_solution

But I am getting this error:

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AS task) FROM _user_solution s INNER JOIN _users u ON s.uid = u.uid' at line 3

Any ideas on how I can count all of the tasks that are incomplete to this solution would be much appreciated.

See https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Nov 18 '18 at 11:16
"Any ideas" is not a valid question. See [ask]. Suggest you chop your query down until there is no error then add to get an error then google it. If you still have a problem please read & act on [mcve]. — philipxy, Nov 18 '18 at 11:46

Madhur Bhaiya · Answer 1 · 2018-11-18T11:08:15.900

1

You need to move the AS task aliasing part outside the subquery; outside the closing bracket.

SELECT
    s.*, u.forname, u.surname, u.email, u.tel, p.type,
    (SELECT COUNT(*) FROM _solution_tasks WHERE progress < 100) AS task
FROM _user_solution s
INNER JOIN _users u
    ON s.uid = u.uid
INNER JOIN _payment_plans p
    ON p.pid = s.payment_plan_type
INNER JOIN _solution_tasks t
    ON s.sid = t.assigned_for_solution

edited Nov 18 '18 at 11:08

answered Nov 18 '18 at 11:04

Madhur Bhaiya

28,155
10
49
57

I did try that earlier, probably should of mentioned it in the question but its been frustrating haha it gives me `#1146 - Table 'iezonsol_data.t' doesn't exist` which is telling me that `INNER JOIN _solution_tasks t` is not being passed as `t`. Would it be easier to show my tables? – Jaquarh Nov 18 '18 at 11:06
@Jaquarh check the updated answer. You can directly refer the table name inside the subquery, instead of using alias from the outer query – Madhur Bhaiya Nov 18 '18 at 11:08
I appreciate the answer but this isn't the expected output, every solution has a unique ID and every task links to that ID, I want to count every task linking to that ID in 1 column, not multiple rows - also, this is bringing out ALL tasks for every solution rather than the `INNER JOIN` solution. [This image shows output](https://gyazo.com/1505b909e090a6915ede5646e5186edc) <-- I'd like them in 1 row rather than multiple (I added a where clause to show only 1 rather than all my data) – Jaquarh Nov 18 '18 at 11:11
My desired output would be [something like this](https://gyazo.com/1dea563f0516fc1dcd689978d2b5db02) where for that solution, there is 2 tasks – Jaquarh Nov 18 '18 at 11:13
@Jaquarh updated query should work. Can you set up a https://www.db-fiddle.com/ ? – Madhur Bhaiya Nov 18 '18 at 11:14
I managed to figure it out using your answer and following what you was trying to do, thanks again! – Jaquarh Nov 18 '18 at 11:20

score 1 · Answer 2 · answered Nov 18 '18 at 11:14

1

you can try like below

SELECT
    s.*, u.forname, u.surname, u.email, u.tel, p.type,
    (SELECT COUNT(*) 
          FROM another_table tt1 
          WHERE tt1.taskID=t.taskID --assume taskID is join key 
         and tt1.progress < 100 
    ) AS task
FROM _user_solution s
INNER JOIN _users u
    ON s.uid = u.uid
INNER JOIN _payment_plans p
    ON p.pid = s.payment_plan_type
INNER JOIN _solution_tasks t
    ON s.sid = t.assigned_for_solution

answered Nov 18 '18 at 11:14

Zaynul Abadin Tuhin

31,407
5
33
63

Thanks for the answer but again, this is showing multiple rows rather than 1 row – Jaquarh Nov 18 '18 at 11:18
@Jaquarh share sample data and expected output – Zaynul Abadin Tuhin Nov 18 '18 at 11:19
I managed to figure it out using the ideology of your answer and Madhurs answer, thanks again though! – Jaquarh Nov 18 '18 at 11:20

score 1 · Answer 3 · answered Nov 18 '18 at 11:19

1

I managed to figure out how to do it, I needed to put the progress in the WHERE clause at the bottom since that is what I am trying to query it all against and then I need to select COUNT(*)

SELECT
    s.*, u.forname, u.surname, u.email, u.tel, p.type, COUNT(*) as tasks
FROM _user_solution s
INNER JOIN _users u
    ON s.uid = u.uid
INNER JOIN _payment_plans p
    ON p.pid = s.payment_plan_type
INNER JOIN _solution_tasks t
    ON s.sid = t.assigned_for_solution
WHERE t.progress < 100

This is now giving me how many tasks are allocated to that solution. After doing abit of research, the first COUNT comes out as 0, like an array index starts at 0, so in this case, the solution row itself brings out 0 and then each task associated with the solution adds 1 giving me the correct multiple of tasks.

answered Nov 18 '18 at 11:19

Jaquarh

6,493
7
34
86

Your description about count is very unclear but also does not seem to be correct. (There is no "first count"--count returns the number of rows in a group.) Calling an aggregate function without explicit GROUP BY groups by all columns, which generates one group, then one row per group, so one row. Unfortunately there is just one group & row even for an empty table, which is a poor language design. Also unfortunately the question does not contain a [mcve] so we don't what "this case" is--no clear specification & no example input & desired output. – philipxy Nov 18 '18 at 21:07
The whole query returns over 250 rows, I tested it on a solution I knew had only 2 tasks and it worked fine so quite possibly, the description might be unclear and wrong but its what I got from a source and the query seems to work fine :) P.S, I'd of included my tables if you'd of just asked nicely – Jaquarh Nov 19 '18 at 10:06
1. That comment is not clear. 2. I am telling you things to help you. 3. Nothing in my comments is unnice. Don't shoot the messenger. – philipxy Nov 20 '18 at 01:02
Your comment is unclear, define unnice? @philipxy PS we can do this all day but my SQL now works as expected therefor answers my question if the theory is wrong, feel free to correct where applicable - I got it from the internet and a lot of the facts could of got lost in translation to my own words. – Jaquarh Nov 20 '18 at 09:44
Here is proof of it working on [db fiddle](https://www.db-fiddle.com/f/tpyQB1sutreSMWnbaDwJQP/0) I had to include `GROUP BY` but on my live DB, this isn't needed @philipxy – Jaquarh Nov 20 '18 at 10:16

score 1 · Answer 4 · answered Nov 18 '18 at 11:43

I've changed your query to this and it works:


SELECT
    s.*, u.forname, u.surname, u.email, u.tel, p.type,
    (SELECT COUNT(WRITE_AUTOINCREMENT_ID) AS task FROM t WHERE t.progress < 100)
FROM _user_solution AS s
INNER JOIN _users AS u
    ON s.uid = u.uid
INNER JOIN _payment_plans p
    ON p.pid = s.payment_plan_type
INNER JOIN _solution_tasks AS ttable
    ON s.sid = ttable.assigned_for_solution

How to count rows from an inner joined table

4 Answers4