SQL JOIN to count and SUM issue

Question

I know that I am missing something, I have tried several different ways but I am overlooking (or trying too hard). Can someone please tell me where I am wrong on this SQL?

SELECT id, 
    COUNT(id) AS dupBlocks 
FROM tbl_duplicates8 INNER JOIN (
        tbl_accounts8, 
        tbl_delaccounts, 
        tbl_bad_bots, 
        tbl_log, 
        tbl_ipban, 
        tbl_ipban8
    ) ON (
        tbl_accounts8.SUM(num_attacks) AND 
        tbl_delaccounts.SUM(noattacks) AND 
        tbl_bad_bots.COUNT(id) AND 
        tbl_log.COUNT(id) AND 
        tbl_ipban.COUNT(txt_ip) AND 
        tbl_ipban8.COUNT(ip)
    );

I did notice this MySQL Join two tables count and sum from second table but it gives me a null on return.

Any help would be appreciated.

To further the question for a better answer, this is what I am currently doing:

$statsresults['newIPBan'] = $db->query("SELECT COUNT(ip) AS newIPBan FROM tbl_ipban8;");
$statsresults['oldIPBan'] = $db->query("SELECT COUNT(txt_ip) AS oldIPBan FROM tbl_ipban;");
$statsresults['log_blocks'] = $db->query("SELECT COUNT(id) AS logBlocks FROM tbl_log;");
$statsresults['badbots'] = $db->query("SELECT COUNT(id) AS badBots FROM tbl_bad_bots;");
$statsresults['del_num_attacks'] = $db->query("SELECT SUM(noattacks) AS deltotalattacks FROM tbl_delaccounts;");
$statsresults['num_attacks'] = $db->query("SELECT SUM(num_attacks) AS totalattacks FROM tbl_accounts8;");
$statsresults['dup_blocks'] = $db->query("SELECT COUNT(id) AS dupBlocks FROM tbl_duplicates8;");

Which will return this:

|    ['newIPBan0newIPBan'] = String(6) "289033"
|    ['oldIPBan0oldIPBan'] = String(6) "125723"
|    ['log_blocks0logBlocks'] = String(4) "6481"
|    ['badbots0badBots'] = String(5) "15310"
|    ['del_num_attacks0deltotalattacks'] = String(9) "119494860"
|    ['num_attacks0totalattacks'] = String(8) "25286478"
|    ['dup_blocks0dupBlocks'] = String(6) "179916"

So right now it is calling the database 7 times to get each sum or count. I was hoping to change that to 1 database call and returning the sum of them all.

Can you please give more info on what you are trying to do? e.g. JOIN tbl_duplicates8 to 6 other tables and then return the SUM and COUNT of specific fields? As it stands, the Sql query doesn't make much sense. — StuartLC
– StuartLC, Commented Jan 30, 2014 at 15:04
Please provide sample data and desired results. What is wrong with the SQL is that it is simply not SQL and very hard (impossible?) to figure out what you are trying to do. — Gordon Linoff
– Gordon Linoff, Commented Jan 30, 2014 at 15:07
I updated the question to give an example of what I am currently successfully doing, although it takes 7 database calls to make it happen. I am hoping I can do this all in 1 call and return the total. Thanks — SecureLive
– SecureLive, Commented Jan 30, 2014 at 15:30

Gordon Linoff · Accepted Answer · 2014-01-30 16:06:30Z

2

This is one way that you can combine them:

select (newIPBan + oldIPBan + logBlocks + badBots + deltotalattacks + totalattacks + dupBlocks
       ) as NumIPs
from (SELECT COUNT(ip) AS oldIPBan FROM tbl_ipban8) ipb8 cross join
     (SELECT COUNT(txt_ip) AS newIPBan FROM tbl_ipban) ipb cross join
     (SELECT COUNT(id) AS logBlocks FROM tbl_log) l cross join
     (SELECT COUNT(id) AS badBots FROM tbl_bad_bots) bb cross join;
     (SELECT coalesce(SUM(noattacks), 0) AS deltotalattacks FROM tbl_delaccounts) da cross join
     (SELECT coalesce(SUM(num_attacks), 0) AS totalattacks FROM tbl_accounts8) ta cross join
     (SELECT COUNT(id) AS dupBlocks FROM tbl_duplicates8) d;

edited Jan 30, 2014 at 16:06

answered Jan 30, 2014 at 15:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

3 Comments

SecureLive Over a year ago

I can see how this could work, although it is still returning null. I will play with this for a few to see if I can get this to work. Thanks

Gordon Linoff Over a year ago

@SecureLive . . . It was returning NULL because of the sum(). Those could return NULL, which causes the entire sum in the select to be NULL. Fixed with coalesce().

SecureLive Over a year ago

Also, I noticed newIPBan + newIPBan, changed the second one to oldIPBan in both places which resolved it as will... Will add the coalesce as well... Thank you! That was great, let me buy you a bear, can you send me your paypal address to jeff AT jbrowns DOT com and I will send ya some money for the help. Thanks Again!

StuartLC · Accepted Answer · 2014-01-30 16:14:01Z

Edit

I would personally create a view for the various metrics, along the following lines:

CREATE VIEW vwMetrics AS
    SELECT 'newIPBan' AS Metric, COUNT(ip) AS Value FROM tbl_ipban8
    UNION
    SELECT 'oldIPBan', COUNT(txt_ip) FROM tbl_ipban
    UNION
    SELECT 'logBlocks', COUNT(id) FROM tbl_log
    UNION
    SELECT 'badBots', COUNT(id) AS badBots FROM tbl_bad_bots
    UNION
    SELECT 'deltotalattacks', SUM(noattacks) FROM tbl_delaccounts
    UNION
    SELECT 'totalattacks', SUM(num_attacks) FROM tbl_accounts8
    UNION
    SELECT 'dupBlocks', COUNT(id) FROM tbl_duplicates8;

And then you can aggregate the components:

SELECT SUM(Value) AS TotalOfEverything
FROM vwMetrics;

The benefit of the view is that you can drill down into the components for detail / debugging purposes, rather than arrive at a magical total - there might be some reuse for the view to be had elsewhere in your system.

Prototype Fiddle here

(+Thanks for clarifying the question)

Collectives™ on Stack Overflow

SQL JOIN to count and SUM issue

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related