sum aggregate function ???

[opie_18]

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

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];

[RobDog888]

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.

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];

[opie_18]

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

[RobDog888]

The Database Development Forum

What were the repeating results?

Did you try it with and without the Distinct keyword?

[opie_18]

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???

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

[RobDog888]

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.

[opie_18]

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

THANK YOU....

[opie_18]

oops, now how do I get it to do for all 12 months and all hotels???

[RobDog888]

Thanks We need to change it so it will group and sum for each month. Hold on and I wil give it a shot.

[RobDog888]

Quick Q. Is this for Access or SQL Server?

[opie_18]

Access

[RobDog888]

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

[RobDog888]

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.

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];

[opie_18]

sweet!!! Thanks a bunch...

John

[RobDog888]

No prob. Did it work correctly?

[Ecniv]

Sorry to butt in wouldn't

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

Be easier?
Or does that mess up the query?

[RobDog888]

Yes, much easier. I always seem to do things the hard way.

Format([budget].[drptdate],"mmmm") As DrptMonth,

Should not mess up the results since its basicly doing the same thing but simplier.

[opie_18]

No it didn't work... and now of course I need the same thing for a real world project today.

[RobDog888]

Which query did you try or was it both? Post what your using?

[opie_18]

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

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;

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

[RobDog888]

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.

[opie_18]

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....

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;

[RobDog888]

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