CODEWITHSUNDEEP
mysqlsqlmysql-workbench
cantas
cantas

Reputation: 114

Mysql- One table Two query in one statement

I have a table to store customer expenses like 

Card_id   |   days          | ytl(expense-$)  
1           2014-05-21         50
2           2014-06-12         30
2           2014-06-13         20
3           2014-05-13         20
3           2014-06-20         20
3           2014-05-13         35
3           2014-06-20         26

I need people whose expenses exceed $40 in each of May and June months.

SELECT DISTINCT card_id
FROM expenses
WHERE date(days) > DATE("2014-05-01")
  AND date(days) < DATE("2015-06-30")
GROUP BY card_id HAVING SUM(YTL) >= 40

This query gives me total of two months. So it returns card_id: 1-2-3. I need just 3

I cannot use intersect, because I'm using MySql. I tried inner join but I cannot solve it.

How can i do this?

Best Regards

Upvotes: 0

Views: 61

Answers (1)

Kickstart
Kickstart

Reputation: 21533

One way to do it:-

SELECT card_id
FROM
(
    SELECT card_id, MONTH(days) AS card_month, SUM(YTL) as card_sum
    FROM expenses
    WHERE days BETWEEN "2014-05-01" AND "2015-06-30"
    GROUP BY card_id, card_month
    HAVING card_sum >= 40
) sub0
GROUP BY card_id 
HAVING COUNT(*) = 2

This has a sub query that gets the sum for each card for each month, using HAVIING to check that the sum for each month is more than 40.

This sub query is then checked to find cards where the count is 2 (ie, 2 rows for that car from the sub query, one for each month).

If you need a bigger date range you might need to tweak this to take account of having the same month number in 2 years in the results from the sub query.

Upvotes: 2

Related Questions