mysql get count of multi table with contition

Question

i have 4 table in my database like t1,t2,t3,t4 i want count id from condition like when orderId = 1 and i don't know how i can do that . anyone can help me? its basic question but i'm new in sql query. i use this query and show four column i need just one to set my total cont to another select query.

(SELECT
(SELECT COUNT(id)
FROM c.fa
WHERE foruser = 1
) AS FA  ,
(SELECT COUNT(id)
FROM c.ia
WHERE foruser = 1
) AS IA ,
(SELECT COUNT(id)
FROM c.la
WHERE foruser = 1
) AS LA ,
(SELECT COUNT(id)
FROM c.ta
WHERE foruser = 1
) AS TA ,
(SELECT SUM(FA+IA+LA+TA)) AS TOTAL
)

please edit your post, & show some sample table structure with data so we will try it. — Mohit Kumar
– Mohit Kumar, Commented Jun 7, 2018 at 1:56

user4837435 · Accepted Answer · 2018-06-07 03:54:49Z

1

I find my ANSWER :

SELECT SUM(FA+IA+LA+TA) AS TOTAL FROM (
(SELECT COUNT(id) AS FA FROM c.f WHERE foruser = 1 ) AS FAA,
(SELECT COUNT(id) AS IA FROM c.i WHERE foruser = 1) AS IAA  ,
(SELECT COUNT(id) AS LA FROM c.l WHERE foruser = 1 ) AS LAA ,
(SELECT COUNT(id) AS TA FROM c.t WHERE foruser = 1 ) AS TAA 
)

For SomeOne like me need this.

answered Jun 7, 2018 at 3:54

user4837435

Sign up to request clarification or add additional context in comments.

2 Comments

saikumarm Over a year ago

when you took idea from an answer please appreciate the answer with an upvote or accept the answer

user4837435 Over a year ago

i submit but no effect

Gordon Linoff · Accepted Answer · 2018-06-07 02:28:53Z

0

Your query is basically correct, but to get the total you need another subquery:

SELECT x.*, (FA + IA + LA + TA) as total
FROM (SELECT (SELECT COUNT(id) FROM c.fa WHERE foruser = 1) AS FA ,
             (SELECT COUNT(id) FROM c.ia WHERE foruser = 1) AS IA,
             (SELECT COUNT(id) FROM c.la WHERE foruser = 1) AS LA,
             (SELECT COUNT(id) FROM c.ta WHERE foruser = 1) AS TA
     ) x;

The problem is that you cannot re-use a column alias in the same SELECT.

The above is only one way to write the query. Often, subqueries are put in a FROM clause, allowing:

SELECT FA.cnt, IA.cnt, LA.cnt, TA.cnt,
       (FA.cnt + IA.cnt + LA.cnt + TA.cnt) as total
FROM (SELECT COUNT(id) as cnt FROM c.fa WHERE foruser = 1) FA CROSS JOIN
     (SELECT COUNT(id) as cnt FROM c.ia WHERE foruser = 1) IA CROSS JOIN
     (SELECT COUNT(id) as cnt FROM c.la WHERE foruser = 1) LA CROSS JOIN
     (SELECT COUNT(id) as cnt FROM c.ta WHERE foruser = 1) TA;

edited Jun 7, 2018 at 2:28

answered Jun 7, 2018 at 2:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

user4837435 Over a year ago

thanks for answering - the query number 1 return # FA, IA, LA, TA, total '1', '11', '2', '2', '16' Not total 16 only and the query number two get syntax error : code 1054

Gordon Linoff Over a year ago

@FarHooD . . . If you only want the total, remove the other columns from the SELECT.

Collectives™ on Stack Overflow

mysql get count of multi table with contition

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related