how to group and count in a single query?

Question

I think it should use GroupBy and aggregate functions to do the following steps I require, however I just can’t get this to work: So say I have the table below but with more records Price

         Cost Zone  Zip Code    Weight(lbs) Price   Cost    Freight Value
             5       K0K             1209      0    135     289
             5       K0K             157       0    135     0
             5       K0L             1425      0    135     650
             5       K0L             1425      0    135     1999

I wish to require a query stating that the count of “Freight Value” falls into one of the ranges

          Zip Code  Range 0-500 Range 501-1000  Range 1001-2000
            K0K         2              0                  0
            K0L         0              1                  1

How is this possible to do this ?? if you need me to explain with more detail let me know, thank you up ahead!

related: http://stackoverflow.com/questions/232387/in-sql-how-can-you-group-by-in-ranges — Jacob Midtgaard-Olesen, Jul 18 '12 at 22:06
@JacobMidtgaard-Olesen That link is SQL Server. SQL is a generic tag, the particular flavour requested is MS Access. — Fionnuala, Jul 18 '12 at 22:15

Fionnuala · Answer 1 · 2012-07-18T22:23:24.923

1

This will run in MS Access. You will need a range table, then:

TRANSFORM Sum(t1.FreightValue) AS SumFreightValue
SELECT t1.ZipCode
FROM t1, Range
WHERE (((t1.FreightValue) Between [range].[Upper] And [range].[lower]))
GROUP BY t1.ZipCode
PIVOT Range.Range

Range table

Range      Lower   Upper
0-500          0     500
501-1000     501    1000
1001-2000   1001    2000

edited Jul 18 '12 at 22:23

answered Jul 18 '12 at 22:11

Fionnuala

90,370
7
114
152

how to group and count in a single query?

1 Answers1