Calculating fields by date

[xconspirisist]

This is rather a strange problem, one that might be hard to describe but I will do my best. :)

I need to calculate fields for various dates and have them returned as a single result set. The desired result set would be something like listing1 (code section)

That result set needs to come from the listing2 (some dummy data included);

Each "month" field is calculated according to the formula in listing3.

So far I have cobbled some SQL together, as seen in listing4.

Currently the query executes, yet it returns zero results. I feel it's close if not rather nasty, but I'm missing something.

I believe the issue is caused by the implicit joins or WHERE criteria.

Can anyone help me fix the query?


listing1:
----------------------------------------------------------------------------
| id | name | month1 | month2 | month3 | month4 |
----------------------------------------------------------------------------
| 01 | Alice | 2 | 3 | 1 | 7 |
| 02 | Bob | 1 | 2 | 5 | 0 |
| 03 | Charles | 6 | 3 | 8 | 9 |
----------------------------------------------------------------------------



listing2:
+-----+----------+------+------------+
| id | name | snum | fdate |
+-----+----------+------+------------+
| 86 | Charles | 84 | 2009-09-30 |
| 265 | Bob | 47 | 2009-09-29 |
| 160 | Alice | 149 | 2009-09-14 |
| 80 | Charles | 192 | 2009-09-24 |
| 267 | Bob | 139 | 2009-10-01 |
| 152 | Alice | 176 | 2009-09-06 |
| 141 | Charles | 163 | 2009-11-25 |
| 316 | Bob | 51 | 2009-11-20 |
| 133 | Charles | 83 | 2009-11-17 |
| 235 | Alice | 1 | 2009-11-29 |
+-----+----------+------+------------+



listing3:
$rowCount = The count of rows for a single name in a date period, where snum is under 100.
$rowCount / 30 * 100



listing4:
SELECT
ful.name,
(count(month1.snum) / 30 * 100) as month1calc,
(count(month2.snum) / 30 * 100) as month2calc
FROM
mytable as ful,
(SELECT id, snum FROM mytable WHERE Month(fdate) = 11 ) as month1,
(SELECT id, snum FROM mytable WHERE Month(fdate) = 10 ) as month2
WHERE
(ful.snum < 100)
AND ful.id = month1.id
AND ful.id = month2.id
GROUP BY
ful.name
ORDER BY
ful.name
;


Many thanks in advance for your help, this issue has kept me amused all day :)

[Old Pedant]

*IF* I understand the problem, should be able to do this:

SELECT name,
( SUM( IF(MONTH(fdate)=11,1,0) ) * 3.33 ) as month11,
( SUM( IF(MONTH(fdate)=10,1,0) ) * 3.33 ) as month10
FROM mytable
WHERE snum < 100 AND MONTH(fdate) IN (11,10)
GROUP BY name
ORDER BY name;

No? The code in red isn't strictly needed, but it might improve performance.

Might point out that if you have data in the table from multiple years, this will get the overall values for *ALL* years.

Possibly you want to addAND YEAR(fdate) = 2008 or similar to the WHERE clause?

[xconspirisist]

That is superb, thank you so very much.

You answered my question, yet the query is actually intended for a MS Access database, which does not support the IF function :( Is there an alternative method you may be aware of, using pure SQL?

edit: MS Access is baffeling, the "if" function is actually called "iif". Joy.

[Old Pedant]

As you noted, Access and MySQL are *IDENTICAL* in usage of IF...excepting only that Access spells it "IIF".

This can also be made to work in MySQL or SQL Server using CASE WHEN in place of IF/IIF.

SELECT name,
( SUM( CASE WHEN MONTH(fdate)=11 THEN 1 ELSE 0 END ) * 3.33 ) as month11,
( SUM( CASE WHEN MONTH(fdate)=10 THEN 1 ELSE 0 END ) * 3.33 ) as month10
FROM mytable
WHERE snum < 100 AND MONTH(fdate) IN (11,10)
GROUP BY name
ORDER BY name;

Luckily, all 3 DBs support the MONTH( ) function.

[guelphdad]

You answered my question, yet the query is actually intended for a MS Access database, which does not support the IF function

Then why post your question in a mysql forum? they are completely different database applications.