Advanced MySQL Statments

[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.com/?op7ku1mk4erp4m6

 

 

 

Thanks for any help in advance.

Edited December 4, 2011 by sbsmith

[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.