SQL query help using two WHERE clauses

I have a table with data spanning about two weeks. I want to see the average for the first 7 days and then the next 8.

I have tried various JOINS with no luck. I am new to SQL so I am probably missing something simple.

Basically these queries work. How do I combine them?

select count(Field)/8 
from TABLE 
WHERE Publish_date >= '04/05/19'

select count(Field)/7 
from TABLE 
WHERE Publish_date < '04/05/19'

Answer

If you really need to combine them, then you can do sub-queries:

SELECT
    (
        SELECT SUM(Field)/8 
        FROM TABLE 
        WHERE Publish_date >= '04/05/19'
    ) as date1,
    (
        SELECT SUM(Field)/7 
        FROM TABLE 
        WHERE Publish_date < '04/05/19'
    ) as date2

---

Please note that you wish to use SUM instead of COUNT, because COUNT just get rows count, not it’s values summed up.