Databases
StackOverflow2013
Postsdbo
POHow to Group By Year and Month in MySQL
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to Group By Year and Month in MySQL
    primarykey
    data
    text
    <p>I would like to measure the count of ID and ATTRIBUTE from the source table and present the data as shown in the "Desired Report" below. I am using MySQL.</p> <p><strong>Source:</strong></p> <pre><code>ID | DATE | ATTRIBUTE -------------------------------- 1 | 2012-01-14 | XYZ 2 | 2012-03-14 | 3 | 2012-03-15 | XYZ 4 | 2012-04-24 | ABC 5 | 2012-04-10 | 6 | 2012-05-11 | ABC </code></pre> <p><strong>Desired Reports:</strong></p> <blockquote> <p><strong>Count of Attribute</strong></p> </blockquote> <pre><code> YEAR | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC --------------------------------------------------------------------------- 2010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 2011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 2012 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 </code></pre> <blockquote> <p><strong>Count of ID</strong></p> </blockquote> <pre><code> YEAR | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC --------------------------------------------------------------------------- 2010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 2011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 2012 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 </code></pre> <blockquote> <p><strong>Percentage Complete ( Count of Attribute / Count of ID )</strong></p> </blockquote> <pre><code> YEAR | JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC --------------------------------------------------------------------------- 2010 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 2011 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 2012 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 </code></pre> <p>Here's the code I have so far. Thanks! And Also keep in mind, I need to extract the Month from the date field in my data but not sure how. Thanks.</p> <pre><code>SELECT YEAR(document_filing_date),MONTH(document_filing_date),COUNT(aif_id) FROM (a_aif_remaining) GROUP BY YEAR(document_filing_date),MONTH(document_filing_date); </code></pre> <p>Suggested answer doesn't work!! Not sure why, here is the error I get:</p> <pre><code>"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' COUNT(CASE WHEN MONTH(document_filing_date) = 1 THEN aif_id END) AS Jan, CO' at line 1" SELECT YEAR(document_filing_date, COUNT(CASE WHEN MONTH(document_filing_date) = 1 THEN aif_id END) AS Jan, COUNT(CASE WHEN MONTH(document_filing_date) = 2 THEN aif_id END) AS Feb, COUNT(CASE WHEN MONTH(document_filing_date) = 3 THEN aif_id END) AS Mar, COUNT(CASE WHEN MONTH(document_filing_date) = 4 THEN aif_id END) AS Apr, COUNT(CASE WHEN MONTH(document_filing_date) = 5 THEN aif_id END) AS May, COUNT(CASE WHEN MONTH(document_filing_date) = 6 THEN aif_id END) AS Jun, COUNT(CASE WHEN MONTH(document_filing_date) = 7 THEN aif_id END) AS Jul, COUNT(CASE WHEN MONTH(document_filing_date) = 8 THEN aif_id END) AS Aug, COUNT(CASE WHEN MONTH(document_filing_date) = 9 THEN aif_id END) AS Sep, COUNT(CASE WHEN MONTH(document_filing_date) = 10 THEN aif_id END) AS Oct, COUNT(CASE WHEN MONTH(document_filing_date) = 11 THEN aif_id END) AS Nov, COUNT(CASE WHEN MONTH(document_filing_date) = 12 THEN aif_id END) AS Dec, FROM a_aif_remaining GROUP BY YEAR(document_filing_date); </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USBen
    1. USBen
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTDuplicate
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to Group By Year and Month in MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POHow to Group By Year and Month in MySQL
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POHow to Group By Year and Month in MySQL
      1. USharry
      1. VTFavorite
    1. CO[What have you tried?](http://mattgemmell.com/2008/12/08/what-have-you-tried/)
      singulars
      1. POHow to Group By Year and Month in MySQL
      1. USKenny
    2. COI've updated the question a tad. You can see the extent of my efforts. I've got the columns as a number and not "MMM" format and it's not yet grouped by year also. I want a matrix.
      singulars
      1. POHow to Group By Year and Month in MySQL
      1. USBen
    3. CO@BenJones you forgot a `)` after Year, that's why the suggested answer doesn't work. My solution counts the presence of an attribute (it won't count rows if attribute is null), your accepted answer counts the presence of a specific attribute, it's not clear from your question what you are really after
      singulars
      1. POHow to Group By Year and Month in MySQL
      1. USfthiella
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload