Newbie help! Grouping by date

• jeff.joseph85

  SSC Rookie

  Points: 34

  More actions

  November 24, 2015 at 8:02 am

  #399375

  Hey guys. We're starting to use SQL in Microsoft Access 2013 at my office and I'm new so bare with me please

  The result of the query below has a column (MAS.JMT_DATE) with dates formatted as mm-dd-yyyy (e.g. 10/16/2007).

  The MAS.JMT_AMT column is money and MAS.CRED_NAME is a string, all = "GFF"

  Rather than having 2695 records ranging from the year 10/1/2007 - 11/23/2015 in the JMT.DATE column, I want to group this query by year so that it sums the JMT.AMT by year. So the result would look something like this:

  2007 100,430.23 GFF

  2008 34,221.23 GFF

  2009 55,555.55 GFF

  etc...

  Can anybody help to assist with the query below (which executes just fine by the way). Thank you!!

  SELECT MAS.JMT_DATE AS "Judgment Date",

  MAS.JMT_AMT AS "Judgment Total",

  MAS.CRED_NAME AS "Creditor Name"

  FROM DBO_MASTER AS MAS

  WHERE MAS.JMT_DATE is Not Null AND MAS.CRED_NAME="GFF"

  ORDER BY MAS.JMT_DATE;

• Scott-144766

  SSCarpal Tunnel

  Points: 4333

  More actions

  November 24, 2015 at 8:14 am

  #1842069

  Assuming that JMT_DATE is stored as a date rather than a string, this should do the trick

  SELECT YEAR(MAS.JMT_DATE) AS "Judgment Year",

  MAS.CRED_NAME AS "Creditor Name",

  SUM(MAS.JMT_AMT) AS "Judgment Total"

  FROM DBO_MASTER AS MAS

  WHERE MAS.JMT_DATE is Not Null AND MAS.CRED_NAME="GFF"

  GROUP BY YEAR(MAS.JMT_DATE),

  MAS.CRED_NAM

  ORDER BY YEAR(MAS.JMT_DATE);

  Scott

  --
  Scott

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  November 24, 2015 at 8:15 am

  #1842070

  If you're working on Access, the syntax might be slightly wrong. However, the logic is there so you can correct it.

  If you're working on SQL Server, you should change your double quotes to single quotes for string literals ("GFF" to 'GFF').

  SELECT YEAR(MAS.JMT_DATE) AS "Judgment Year",

  SUM(MAS.JMT_AMT) AS "Judgment Total",

  MAS.CRED_NAME AS "Creditor Name"

  FROM DBO_MASTER AS MAS

  WHERE MAS.JMT_DATE is Not Null AND MAS.CRED_NAME="GFF"

  GROUP BY YEAR(MAS.JMT_DATE), MAS.CRED_NAME

  ORDER BY MAS.JMT_DATE;

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2