Is there something like SUM(DISTINCT column) ???

acidbox

I have two tables like this:

CASES TABLE
+-----------+------------------+
case | days |
+-----------+------------------+
| 1 | 2 |
| 2 | 2 |
| 3 | 5 |
| 4 | 1 |
| 5 | 3 |
| 6 | 4 |
| 7 | 5 |
+-----------+------------------+

EVENTS TABLE
+-----------+------------------+--------------------+
event | case | datestamp |
+-----------+------------------+--------------------+
| 1 | 1 | 2004-01-02 |
| 2 | 1 | 2004-02-11 |
| 3 | 1 | 2004-02-22 |
| 4 | 2 | 2004-02-05 |
| 5 | 1 | 2004-02-15 |
| 6 | 3 | 2004-03-12 |
| 7 | 1 | 2004-04-28 |
+-----------+------------------+--------------------+

I need to find the count of individual cases (Which I can do), the month in which the even occured (Which I can also do), and the total number of days for each particular case (which I am having trouble with). Look at this.

SELECT COUNT(DISTINCT c.case) AS case_count, SUM(c.days) AS sum_days, DATEFORMAT(e.datestamp, '%m') AS event_month FROM cases c, events e WHERE c.case = e.case GROUP BY event_month;

I only want to SUM(c.days) ONE TIME for each case. Right now, I am getting the right number of cases for each month, but the sum of the days is compounding each time a particluar case is in the events table.

For instance, if you look at the above example table. There are 4 events in February 2004. Three of the events are for case 1, and one event is for case 2. With my SQL statement, it returns 2 cases (which is correct) and 8 days (which is not correct). It should only be 4 days, 2 for case 1 and 2 for case 2.

Can someone help me figure out this SUM() function so that it only sums the days one time for each case, even if the case is counted more than one time in the events table?

nemonoman

what exactly is the 'event_month' you are grouping by?

I think you want to SELECT

WHERE datestamp LIKE '2005-02%'
GROUP BY e.case

acidbox

Originally posted by nemonoman
what exactly is the 'event_month' you are grouping by?

I think you want to SELECT

WHERE datestamp LIKE '2005-02%'
GROUP BY e.case

I am trying to get a result of 12 records, one for each month. Each month will contain the COUNT of the number of DISTINCT cases and a DISTINCT sum of the days accounted for for that PARTICULAR case

nemonoman

Try my solution before telling me why it won't work.

Sxooter

Has your database got a function like postgresql's date_trunc

If so, you could do something like:

select count(somefield) from sometable group by date_trunc('month',datefeld);

nemonoman

Well, if it comes down to that, he could use a boatload of different MySQL date functions.

example:
GROUP BY date_format(datestamp, '%Y%m')

If the datestamp column is a column of type DATE, of which I am sadly very doubtful. Probably it's a varchar being populated god knows how, in which case

WHERE datestamp LIKE '2005%'
GROUP BY SUBSTRING(datestamp, 5,2)

should do the trick.

Sxooter

Why is it that so many folks who use mysql are afraid of the date and timestamp types? 🙂