[RESOLVED] Grouping Multiple Counts in One Query

[kaiserthegreat]

Say I have the following table and contents:

Item, Date, Finished
Item 1, 1939-02-13, YES
Item 2, 1939-05-13, NO
Item 3, 1975-12-1, YES

I'm trying to create a query that groups counts by decade and tells me how many entires I have total vs how many entries I have finished AKA a result like:

Decade, Finished, Total
1930, 1, 2
1970, 1, 1

Any thoughts?

[kaiserthegreat]

Nevermind...duh...

SELECT LEFT(Date,3) AS 'decade', Finished, COUNT(*) FROM items GROUP BY decade, finished

I was trying way too hard.

[bradgrafelman]

First, thing about how you figure out which "decade" a year is in; you're essentially doing "year - (year MOD 10)" right? Mathematically, that'd be written as:

Year - (Year % 10)

Now just add in the MySQL YEAR() function and you've got your GROUP BY statement.

EDIT: Or... use a string approach, if that works for you.