mysql PHP query to contain count over join

Question

I have this query which after checking various tutorials should work - but it doesn't.

$query="SELECT week, year, COUNT(week) AS week_no
FROM archive_agent_booking
LEFT JOIN invoice_additions ON invoice_additions.week = archive_agent_booking.week
WHERE client_id='$account_no' GROUP BY week, year ORDER BY week DESC";

The tables are as follows:

archive_agent_booking

+---------+----------+----------+----------+----------+---------+---------+
| job_id  |   week   |   year   |   desc   |   price  |   date  | acc_no  |
+---------+----------+----------+----------+----------+---------+---------+


invoice_additions

+---------+----------+----------+----------+----------+---------+
| acc_no  |  week    |   year   |  desc    | am_price | am_date |
+---------+----------+----------+----------+----------+---------+

I basically want to count each week element from both tables and display them as one total even if one of the week values does not show in one of the tables. Don't know whether this is the best solution so I am open to alternatives.

What does your query output?

ethrbunny
– ethrbunny

2012-11-15 14:33:42 +00:00
Commented Nov 15, 2012 at 14:33 — ethrbunny
– ethrbunny, Commented Nov 15, 2012 at 14:33
just comes up with the set die() statement.

Sideshow
– Sideshow

2012-11-15 14:52:43 +00:00
Commented Nov 15, 2012 at 14:52 — Sideshow
– Sideshow, Commented Nov 15, 2012 at 14:52

Chris Charles · Accepted Answer · 2012-11-15 14:37:52Z

1

select 
    week, 
    sum(items) 
from 
    (
        (select week, count(*) as items from archive_agent_booking group by week)
    union 
        (select week, count(*) from invoice_additions group by week)
    ) 
group by 
    week

Edit: i've made some huge assumption about what you want to see

answered Nov 15, 2012 at 14:37

Chris Charles

4,44620 silver badges31 bronze badges

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

1 Comment

Sideshow Over a year ago

Thanks :) This seems to work on the surface although I have not got time to test it - next half an hour...

Collectives™ on Stack Overflow

mysql PHP query to contain count over join

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related