SQL for my requirement?

I need a SQL query to list all the customers who have joined in last 6 months.

This is my SQL for that.

    select 
    c.company_name,
    c.phone1,
    c.sprovince开发者_StackOverflow,
    c.scountry,
    sum(order_total_amount) as amt_sold,
    max(o.order_date) as last_order_date,
    customersince
from
        tbl_company c
join
        tbl_order o
    on c.companyid = o.company_id
where
    c.companytype like 'Customer'
and
    (PERIOD_DIFF(c.customersince,curdate())<6)
group by company_name
order by amt_sold desc

Now I need one more or condition so that if there is null in customersince column then I should check the first order of the customer. If it is in last 6 months I should display that user also.

order_date is available in tbl_order table. The first order of the customer is min(order_date) group by customer_id

How can I do this?

---

SELECT  c.company_name,
        c.phone1,
        c.sprovince,
        c.scountry,
        SUM(order_total_amount) as amt_sold,
        MAX(o.order_date) as last_order_date,
        COALESCE(customersince, MIN(o.order_date)) AS customersince
FROM    tbl_order o
JOIN    tbl_company c
ON      c.companyid = o.company_id
WHERE   c.companytype like 'Customer'
        AND (c.customersince >= NOW() - INTERVAL 6 MONTH OR c.customersince IS NULL)
GROUP BY
        companyid
HAVING  customersince >= NOW() - INTERVAL 6 MONTH
ORDER BY
        amt_sold DESC

Note the double condition on customersince: one in the WHERE clause, another one in the HAVING clause.

If you have an index on tbl_customer (customersince), this index will be used to filter the appropriate records early (and fine-filter them later).

---

PERIOD_DIFF(ifnull(c.customersince, `first_order`),curdate())<6)

you should include your table schema, your first order is refer to which table?

---

I don't have mysql to test, but this where clause might work for you:

where c.companytype like 'customer' 
and ( 
    (PERIOD_DIFF(c.customersince, curdate())<6)
    OR
    (c.customersince is null AND (PERIOD_DIFF(o.order_date, curdate())<6)
)

Also, you will have to add things you don't have in an aggregate function (max, sum, etc) to your group by clause.