2 Fields, 1 Query

November 10, 2009 3:23 PM

SQLfilter: Trying to sum and count two fields contained within the table by specifying criteria (more inside).

I have two fields a service_number field and a minutes field.

I need two operations performed at once, I need to count the service_number field and sum the minutes field.

BUT I need to do it where the minutes field is less than 1000. I also need the same information where the minutes field is between 1001 and 2000 and where the minutes field is between 2001 and 3000...and so on, and I need it all done in 1 query.

I've been trying to do this all day, SQL Guru's do your magic.

posted by kasperj74 to Computers & Internet (4 answers total)

The simplest way would be something like:

SELECT FLOOR(minutes / 1000) AS minute_group, COUNT(service_number), SUM(minutes)
FROM sometable
GROUP BY minute_group

You may have to tweak the GROUP BY to explicitly specify FLOOR(minutes / 1000) or even replace FLOOR with the appropriate floor function depending on your DBMS.

If you have arbitrary groupings other than thousands, you could use something like this to specify the criteria:

SELECT '< 1000' AS label, COUNT(service_number) AS service_number_count, SUM(minutes) AS minutes_sum
FROM ...
WHERE minutes < 1000
UNION ALL
SELECT '1001 - 2000' AS label, COUNT(service_number) AS service_number_count, SUM(minutes) AS minutes_sum
FROM ...
WHERE minutes BETWEEN 1001 AND 2000
UNION ALL
etc.

I know from experience that you may need it all in one row, though, and if that's the case, you want something more like (this is evil):

SELECT
COUNT(CASE WHEN minutes < 1000 THEN service_number ELSE NULL END),
SUM(CASE WHEN minutes < 1000 THEN minutes ELSE 0 END),
COUNT(CASE WHEN minutes BETWEEN 1001 AND 2000 THEN service_number ELSE NULL END),
SUM(CASE WHEN minutes BETWEEN 1001 AND 2000 THEN minutes ELSE NULL END),
.....

posted by pocams at 3:56 PM on November 10, 2009

Whenever I've needed to do this kind of thing, I've found it easiest to create a second table that defines the ranges I want, and use a join to match every row in the table of interest with a row in the ranges table.

For this example I'd create a table, let's call it "ranges", that looks kind of like this:

low      high       
0        1000
1000     2000
2000     3000

... and so on up to whatever the biggest range needed is.

Then the actual query looks kind of like this, assuming your table is called "services"

select low,high,count(service_number),sum(minutes)
from services
join ranges on services.minutes>=ranges.low and services.minutes<ranges.high
group by low,high
order by low

This will give you one output row for each row in the range table, with count and sum from all services records whose minutes value falls within the defined range.

The ranges I'm showing here are little different than what you asked for though... I wasn't sure if "minutes" was an integer where you could guarantee that a value like 1000.5 won't exist -- because if it can, it could fall between a range that ends at 1000 and the next one starting at 1001. So the first range I show in the ranges table acts like a range from 0 up to, but not including, 1000... then the second range starts at 1000, etc.

Another nice thing about doing it this way is you can add a textual description column to the ranges table and output that instead or as well as the actual numeric ranges. The ranges themselves can also be defined arbitrarily, e.g. if you want no more than 10 ranges and you want the last one to be "10,000 minutes+" you can do that this way (make the last range end at 2 billion or some other suitably huge number).
posted by FishBike at 4:33 PM on November 10, 2009

Both of the responses above worked great - now I need to make it a little more complicated. I now need to bring in another table with nearly the same information and match it to the same ranges.

ranges count_service_number1 count_service_number2 sum_mins1 sum_mins2
posted by kasperj74 at 5:32 PM on November 10, 2009

I now need to bring in another table with nearly the same information and match it to the same ranges.

This can be done several ways. You can do it with sub-queries, if your SQL implementation supports them. Keep the ranges table from my previous answer, and let's say your two data tables are "servicesa" and "servicesb":

select
	low,
	high,
	(select count(service_num)
              from servicesa
              where servicesa.minutes>=low
              and servicesa.minutes<high),
	(select sum(minutes)
              from servicesa
              where servicesa.minutes>=low
              and servicesa.minutes<high),
	(select count(service_num)
              from servicesb
              where servicesb.minutes>=low
              and servicesb.minutes<high),
	(select sum(minutes)
              from servicesb
              where servicesb.minutes>=low
              and servicesb.minutes<high)
from ranges
order by low

There are other ways to get the same result, this is just the first one that occurred to me.
posted by FishBike at 5:51 PM on November 10, 2009