sum aggregate function ???

Printable View

Feb 9th, 2005, 10:54 AM
opie_18

sum aggregate function ???

This access query is returning the sum amount for days 1-31 in January... so I see 31 rows of Data... they all have the same account number... I want to see 1 row of data summed up for all of January... what am I forgetting

Thanks,

John

Code:

SELECT DISTINCT [Budget].[Hotel],[Budget].[DrptDate], [Budget].[Account], [Budget].[Description], [Budget].[Amount], Sum([Budget].[Amount]) AS SumOfAmount FROM Budget WHERE ((([Budget].[Account])="10-41220")) And month([budget].[drptdate])=1 and budget.hotel = "CQSC" GROUP BY ([Budget].[DrptDate]), [Budget].[Hotel], [Budget].[Account], [Budget].[Description], [Budget].[Amount];

Re: sum aggregate function ???

This would be better in the DB Forum, but I can answer it.
I'm not sure about the Distinct clause being
needed since your summing and grouping. Also, your grouping on each day in the month of Janurary so you can not get
one row for a result unless you used a alias field and set the value to "Janurary". See if this helps get you closer.
VB Code:
SELECT 
 [Budget].[Hotel],
 "Janurary" As DrptMonth,
 [Budget].[Account],
 [Budget].[Description],
 [Budget].[Amount],
 Sum([Budget].[Amount]) AS SumOfAmount
FROM
 Budget
WHERE
 (
  (
   ([Budget].[Account])="10-41220")
  )
  And
 Month([budget].[drptdate])=1 
  And
 budget.hotel = "CQSC"
GROUP BY
 DrptMonth,
 [Budget].[Hotel],
 [Budget].[Account],
 [Budget].[Description],
 [Budget].[Amount];

Feb 9th, 2005, 01:32 PM
opie_18

Re: sum aggregate function ???

hey,

I did what you said and it brought my query back with 4 rows... hmmm...

oh and where is the other forum you suggested...

Thanks,

John
Feb 9th, 2005, 01:48 PM
RobDog888

Re: sum aggregate function ???

The Database Development Forum

What were the repeating results?

Did you try it with and without the Distinct keyword?
Feb 9th, 2005, 02:35 PM
opie_18

Re: sum aggregate function ???

not sure... when I replaced the date column with your suggestion I get the results below... if I put my date column back in with your suggestion I get all 31 days back again...

I wonder if there were spaces in the hotel column or account column would be the reason???

Code:

Hotel DrptMonth Account Description Amount SumOfAmount CQSC Janurary 10-41220 Business Travel Club -820 -820 CQSC Janurary 10-41220 Business Travel Club -492 -492 CQSC Janurary 10-41220 Business Travel Club -410 -820 CQSC Janurary 10-41220 Business Travel Club -246 -246 CQSC Janurary 10-41220 Business Travel Club -164 -1312 CQSC Janurary 10-41220 Business Travel Club -82 -1476
Feb 9th, 2005, 02:44 PM
RobDog888

Re: sum aggregate function ???

Its the Amount field. You can not group and sum and still have a detail of the group. Its like grouping
and then ungrouping. Take out the Amount field and see how it get afected.
Feb 9th, 2005, 02:47 PM
opie_18

"SOLVED!!!!"Re: sum aggregate function ???

you are the man!!!!!!!!

THANK YOU....
Feb 9th, 2005, 02:49 PM
opie_18

Re: sum aggregate function ???

oops, now how do I get it to do for all 12 months and all hotels???
Feb 9th, 2005, 02:51 PM
RobDog888

Re: sum aggregate function ???

Thanks :thumb: We need to change it so it will group and sum for each month. Hold on and I wil give it a shot.
Feb 9th, 2005, 02:53 PM
RobDog888

Re: sum aggregate function ???

Quick Q. Is this for Access or SQL Server?
Feb 9th, 2005, 03:26 PM
opie_18

Re: sum aggregate function ???

Access
Feb 9th, 2005, 03:55 PM
RobDog888

Re: sum aggregate function ???

How do you want the dates displayed as - "Janurary" or 01/2005?

Re: sum aggregate function ???

See how this is. I didnt have a db to test against and I assummed that it would be
for all hotels and all accounts for 2005 and in a month date format.

VB Code:

SELECT 
 [Budget].[Hotel],
 IIF(Month([budget].[drptdate])=1,
  "Janurary",
   IIF(Month([budget].[drptdate])=2,
    "Febraury",
     IIF(Month([budget].[drptdate])=3,
      "March",
       IIF(Month([budget].[drptdate])=4,
        "April",
         IIF(Month([budget].[drptdate])=5,
          "May",
           IIF(Month([budget].[drptdate])=6,
            "June",
             IIF(Month([budget].[drptdate])=7,
              "July",
               IIF(Month([budget].[drptdate])=8,
                "August",
                 IIF(Month([budget].[drptdate])=9,
                  "September",
                   IIF(Month([budget].[drptdate])=10,
                    "October",
                     IIF(Month([budget].[drptdate])=11,
                      "November","December"))))))))))) As DrptMonth,
 [Budget].[Account],
 [Budget].[Description],
 Sum([Budget].[Amount]) AS SumOfAmount
FROM
 Budget
WHERE
 Year([budget].[drptdate])=2005 
GROUP BY
 DrptMonth,
 [Budget].[Hotel],
 [Budget].[Account],
 [Budget].[Description];

Feb 10th, 2005, 03:11 PM
opie_18

Re: sum aggregate function ???

sweet!!! Thanks a bunch...

John
Feb 10th, 2005, 03:22 PM
RobDog888

Re: sum aggregate function ???

No prob. :thumb: Did it work correctly?
Feb 11th, 2005, 10:05 AM
Ecniv

Re: sum aggregate function ???

Sorry to butt in wouldn't

Code:

SELECT [Budget].[Hotel], Format([budget].[drptdate],"mmmm") As DrptMonth, [Budget].[Account], ...

Be easier?
Or does that mess up the query?
Feb 11th, 2005, 11:51 AM
RobDog888

Re: sum aggregate function ???
Yes, much easier. I always seem to do things the hard way. :sick:
VB Code:

Format([budget].[drptdate],"mmmm") As DrptMonth,
Should not mess up the results since its basicly doing the same thing but simplier.

:thumb:
Feb 11th, 2005, 02:18 PM
opie_18

Re: sum aggregate function ???

No it didn't work... and now of course I need the same thing for a real world project today.
Feb 11th, 2005, 02:36 PM
RobDog888

Re: sum aggregate function ???

Which query did you try or was it both? Post what your using?
Feb 11th, 2005, 02:53 PM
opie_18

Re: sum aggregate function ???

Lets try this approach...
This code works correctly... however, adding the Date to the select adds thousands of rows... i need to have the a column that represents the date
of 1/31/05, and 2/31/05 and so on... a psudocolumn if you will... so that I can import properly... to another table

Code:

SELECT hotel, account, Description, sum(amount) FROM Budget where account = '199-41200' and month(drptdate) = 1 and year(drptdate) = '2005' group by hotel, account, Description;

Code:

hotel account Description Expr1003 ABVA 199-41200 Rooms Rented - Rack/Corp Transient 445 ACMI 199-41200 Rooms Rented - Rack/Corp Transient 1146 AFMI 199-41200 Rooms Rented - Rack/Corp Transient 649 AHGA 199-41200 Rooms Rented - Rack/Corp Transient 223 BNNC 199-41200 Rooms Rented - Rack/Corp Transient 543 BRGA 199-41200 Rooms Rented - Rack/Corp Transient 53 CANC 199-41200 Rooms Rented - Rack/Corp Transient 601 CCVA 199-41200 Rooms Rented - Rack/Corp Transient 75 CEFL 199-41200 Rooms Rented - Rack/Corp Transient 166 CGNY 199-41200 Rooms Rented - Rack/Corp Transient 808 CHNC 199-41200 Rooms Rented - Rack/Corp Transient 273 CMNC 199-41200 Rooms Rented - Rack/Corp Transient 416 CNSC 199-41200 Rooms Rented - Rack/Corp Transient 492 CQSC 199-41200 Rooms Rented - Rack/Corp Transient 540 DCNC 199-41200 Rooms Rented - Rack/Corp Transient 828 DHNC 199-41200 Rooms Rented - Rack/Corp Transient 218 DUNC 199-41200 Rooms Rented - Rack/Corp Transient 673 EGIL 199-41200 Rooms Rented - Rack/Corp Transient 1004 FCNC 199-41200 Rooms Rented - Rack/Corp Transient 347 GVSC 199-41200 Rooms Rented - Rack/Corp Transient 272 GWGA 199-41200 Rooms Rented - Rack/Corp Transient 641 HCNC 199-41200 Rooms Rented - Rack/Corp Transient 92 HHSC 199-41200 Rooms Rented - Rack/Corp Transient 262 HMTX 199-41200 Rooms Rented - Rack/Corp Transient 1161 HPAZ 199-41200 Rooms Rented - Rack/Corp Transient 366 JVNC 199-41200 Rooms Rented - Rack/Corp Transient 685 LHFL 199-41200 Rooms Rented - Rack/Corp Transient 434 OCFL 199-41200 Rooms Rented - Rack/Corp Transient 217 PHGA 199-41200 Rooms Rented - Rack/Corp Transient 241 PVFL 199-41200 Rooms Rented - Rack/Corp Transient 244 RGNC 199-41200 Rooms Rented - Rack/Corp Transient 1086 RHNC 199-41200 Rooms Rented - Rack/Corp Transient 1199 RPAZ 199-41200 Rooms Rented - Rack/Corp Transient 700 SCNJ 199-41200 Rooms Rented - Rack/Corp Transient 804 SHMA 199-41200 Rooms Rented - Rack/Corp Transient 147 SLGA 199-41200 Rooms Rented - Rack/Corp Transient 428 THNJ 199-41200 Rooms Rented - Rack/Corp Transient 131 WCNC 199-41200 Rooms Rented - Rack/Corp Transient 278 WGGA 199-41200 Rooms Rented - Rack/Corp Transient 214 WMNC 199-41200 Rooms Rented - Rack/Corp Transient 548 WPNY 199-41200 Rooms Rented - Rack/Corp Transient 784
Feb 11th, 2005, 03:05 PM
RobDog888

Re: sum aggregate function ???

I thought thats what I had on my big query I posted? The psuedocolumn was DrptMonth.
You need to Group By on the psuedocolumn also in order to eliminate the
mass record returns.
Feb 11th, 2005, 03:15 PM
opie_18

Re: sum aggregate function ???

I did that and I get this error

"you tried to execute a query that does not include the specified expression..."

I am doing something wrong....

Code:

SELECT IIF(Month([budget].[drptdate])=1, "Janurary", IIF(Month([budget].[drptdate])=2, "Febraury", IIF(Month([budget].[drptdate])=3, "March", IIF(Month([budget].[drptdate])=4, "April", IIF(Month([budget].[drptdate])=5, "May", IIF(Month([budget].[drptdate])=6, "June", IIF(Month([budget].[drptdate])=7, "July", IIF(Month([budget].[drptdate])=8, "August", IIF(Month([budget].[drptdate])=9, "September", IIF(Month([budget].[drptdate])=10, "October", IIF(Month([budget].[drptdate])=11, "November","December"))))))))))) As DrptMonth,hotel, account, Description, sum(amount), '01/31/2005' AS [LastDayOfMonth] FROM Budget where year(drptdate) = '2005' and account IN('199-41200', '199-41215', '199-41225', '19941220', '199-42130', '199-41250', '199-41255', '199-41340', '199-41290', '199-41291', '199-41240', '199-41320') group by DrptMonth, hotel, account, Description;
Feb 11th, 2005, 03:20 PM
RobDog888

Re: sum aggregate function ???

What is the last part of the message? It should tell you the name of the offending field.