1 reply to this topic

[sbsmith]

Hi

I'm hoping someone here can help me resolve this problem.

I think I'm almost there I'm just missing something that I've overlooked.

What I'm trying to do is create one custom MySQL table out of 3 existing MySQL Tables.

This is what I've come up with...

SELECT proj.p_id, proj.p_name, proj.p_status,
 COUNT(domain) AS domain_count,
 SUM(IF(pt_status = 3 , 1, 0 )) AS published_posts,
 SUM(IF(pt_status = 2 , 1, 0 )) AS approved_posts,
 SUM(IF(pt_status = 1 , 1, 0 )) AS queued_posts,
 SUM(IF(pt_status < 1 , 1, 0 )) AS declined_posts
 FROM wp_cglg_projects proj
 LEFT JOIN wp_cglg_domains d
 ON proj.p_id = d.project_id
 LEFT JOIN wp_cglg_posts pt
 ON d.d_id = pt.domain_id
 GROUP BY proj.p_id;

The statement above produces the table I want except the domain count in my first row is incorrect. It should
be 2 not 3.

1. Below is picture of my Custom Table...




Here is a picture of my existing 3 tables that I'm referencing to create my custom table.



Mysql Dump File
Finally if you wish to take a look here is my mysql dump file you can download and dump into your test db.

http://www.mediafire...op7ku1mk4erp4m6



Thanks for any help in advance.

Edited by sbsmith, 04 December 2011 - 10:43 AM.

[sbsmith]

This has been resolved by

replacing

COUNT(domain) AS domain_count

On second line with

COUNT(DISTINCT domain) AS domain_count

without using DISTINCT on COUNT It seems to be counting my domains again
which in turn gives me the incorrect number.

I don't fully understand why it was behaving like this
but at least it is finally working the way I intended.