CODEWITHSUNDEEP
mysqlselectcount
kamal
kamal

Reputation: 237

multi count in one query

I have three table like this:

manga

|   id  | sendby
------------------
|   1   |   1
|   2   |   1
|   3   |   1
|   4   |   1

chaprer

|   id  | sendby    | translator | graphic
-------------------------------------------
|   1   |   1       |   admin    |  other
|   2   |   1       |   admin    |  other
|   3   |   1       |   admin    |  admin
|   4   |   1       |   other    |  admin

user

|   userid  | username  
-----------------------
|      1    |   admin

I'm trying to count what ever i have sent, but the result is not correct.

i have tried below query:

SELECT username,
   SUM(
       CASE WHEN m.sendby = u.userid
       THEN 1 ELSE 0 END
   ) AS manga,
   SUM(
       CASE WHEN c.sendby = u.userid
       THEN 1 ELSE 0 END
   ) AS chapter,
   SUM(
       CASE WHEN c.translator = u.username
       THEN 1 ELSE 0 END
   ) AS translator,
   SUM(
       CASE WHEN c.graphic = u.username
       THEN 1 ELSE 0 END
   ) AS graphic
FROM user u
    left JOIN manga m 
        ON m.sendby = u.userid 
    left JOIN chapter c 
        ON c.sendby = u.userid 
where u.userid = '1'

but it's return this which is not correct:

manga : 16, chapter : 16, translator : 12, graphic : 8

Upvotes: 2

Views: 140

Answers (3)

Muhammad Raheel
Muhammad Raheel

Reputation: 19882

Here is the query for this

select
  m.id,
  count(m.sendby) as Manga,
  lc.Chapter,
  rc.Translater,
  c.Grafix
from manga as m
  left join (select sendby, count(sendby) as Chapter from chaprer) as lc on lc.sendby  = m.sendby
  left join (select sendby, count(translater) as Translater from chaprer where translater = 'admin') as rc on rc.sendby  = m.sendby
  left join (select sendby, count(graphics) as Grafix from chaprer where translater = 'admin') as c on c.sendby  = m.sendby

EDITS
I have tested this query and this fetches this result

Query Result
id  Manga   Chapter Translater  Grafix
1   4       4       3           3

Upvotes: 2

c 2
c 2

Reputation: 1167

A straight-forward approach would be to do something like this:

SELECT  username,
(SELECT COUNT(m.sendby) FROM manga m WHERE m.sendby= u.userid) AS manga,
(SELECT COUNT(c.sendby) FROM chapter c WHERE c.sendby= u.userid) AS chapter,
(SELECT COUNT(t.sendby) FROM chapter t WHERE t.translator= u.username) AS translator,
(SELECT COUNT(g.sendby) FROM chapter g WHERE g.graphic= u.username) AS graphic
FROM USER u
WHERE userid=1

You will see the problem in your original query if you run:

SELECT *
FROM USER u
    LEFT JOIN manga m 
        ON m.sendby = u.userid 
    LEFT JOIN chapter c 
        ON c.sendby = u.userid 
WHERE u.userid = '1'

The left join is returning duplicate rows.

Upvotes: 0

Ido.Co
Ido.Co

Reputation: 5467

Did you use left JOIN intentionally? do you know how it works? Using it twice made your select run on 16 rows result table. Try first selecting all the results (*), and then fix your FROM clause to return the desired data to select from.

From your comment, it appears that you ment to use Inner join.

Upvotes: 1

Related Questions