Count same name column from different sql table

Question

I have 3 tables scan_1, scan_2, scan_3. Here is structure of SQL schema:

scan_1:                 scan_2:                   scan_3:
P_no work               P_no work                 P_no work
1    YES                1    YES                  1    NO
2    NO                 2    NO                   2    NO
3    YES                3    YES                  3    NO

I want to count P_no where work ='YES'. But if yes occurred in 2 position LIKE for P_no = 1 which having YES in scan_1 and scan_2 it must be count as 1.

my query is :

SELECT count(`P_no`) AS `ab1` FROM 
`scan_1`,`scan_2`,`scan_3` WHERE 
((`scan_1`.`work`= 'YES') OR 
(`scan_2`.`work`= 'YES') OR 
(`scan_3`.`work`= 'YES'))

Shushil Bohara · Accepted Answer · 2017-11-21 05:08:03Z

1

TRY THIS:

SELECT COUNT(*) AS total_count 
FROM
    (
        SELECT P_no, work FROM scan_1 WHERE work = 'YES'
        UNION
        SELECT P_no, work FROM scan_2 WHERE work = 'YES'
        UNION
        SELECT P_no, work FROM scan_3 WHERE work = 'YES'
    ) AS total

answered Nov 21, 2017 at 5:08

Shushil Bohara

5,6662 gold badges19 silver badges32 bronze badges

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

Comments

Md. Suman Kabir · Accepted Answer · 2017-11-21 05:28:37Z

1

You can achieve your desired result using UNION like below :

SELECT COUNT(*) AS ab1
FROM (
    SELECT P_no, work FROM scan_1 WHERE work='YES'
    UNION
    SELECT P_no, work FROM scan_2 WHERE work='YES'
    UNION
    SELECT P_no, work FROM scan_3 WHERE work='YES'
) as T

SQL HERE

Notice the three queries which is merged using UNION. It merges the result and excludes the duplicate records from the result.

edited Nov 21, 2017 at 5:28

answered Nov 21, 2017 at 5:13

Md. Suman Kabir

5,4615 gold badges29 silver badges45 bronze badges

Comments

Ponni · Accepted Answer · 2017-11-21 05:43:10Z

1

This may work for you as far I understood. Using union for eliminating duplicates.

    select count(*) AS `ab1` FROM
    (
    select `P_no`,`work` from `scan_1` s1 where `work`= 'YES' 
     union
    select `P_no`,`work` from `scan_2` s2 where `work`= 'YES'
     union
     select `P_no`,`work` from `scan_3` s3 where `work`= 'YES' 
    ) as final

edited Nov 21, 2017 at 5:43

Ponni

4081 gold badge5 silver badges17 bronze badges

answered Nov 21, 2017 at 4:46

Newaz Sharif

4244 silver badges12 bronze badges

2 Comments

Shalini Over a year ago

Error : check the manual for the right syntax to use near 'as one union

Newaz Sharif Over a year ago

Sorry MySql is not current installed on my pc. Can you check the edit again?

DineshDB · Accepted Answer · 2017-11-21 05:19:42Z

0

Try this,

SELECT COUNT(DISTINCT P_No) AS ab1
FROM (
    SELECT P_no, work FROM scan_1
    UNION
    SELECT P_no, work FROM scan_2
    UNION
    SELECT P_no, work FROM scan_3
) T
WHERE T.Work='YES'

Hope this helps you.

answered Nov 21, 2017 at 5:19

DineshDB

6,2238 gold badges38 silver badges54 bronze badges

Collectives™ on Stack Overflow

Count same name column from different sql table

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related