Help with SQL Query -- Need Field with Highest Sum

Question

I have a table called NET_REPORT with the following fields (among many):

JOBS     – a number (of jobs)
NET_DATE – date in format YYYY-MM-DD
CODE     – a three digit integer (i.e., 001, 002, etc).

There can be multiple records for a single NET_DATE, each with a different code.
I need to know the maximum number of jobs in a single NET_DATE, over all codes.

I had the query SELECT MAX(JOBS) as MaximumJobs FROM NET_REPORT, but this gives me the maximum jobs for a particular NET_DATE and CODE. Instead, I need to add together the value in the JOBS field for all the records for each NET_DATE, then find the maximum value of all those totals. Any ideas?
Thanks.

Answer 1

Simple Version

SELECT sum(JOBS) as JobCount, NET_DATE FROM NET_REPORT GROUP BY NET_DATE

If you want to order by NET_DATE, starting with the highest Job Count

SELECT * FROM
  (SELECT sum(JOBS) as JobCount, NET_DATE FROM NET_REPORT GROUP BY NET_DATE) AS A
ORDER BY A.JobCount DESC

If you need only the NET_DATE with the highest Job Count

SELECT Top 1 * FROM
  (SELECT sum(JOBS) as JobCount, NET_DATE FROM NET_REPORT GROUP BY NET_DATE) AS A
ORDER BY A.JobCount DESC

References

• Group By Reference
• TSQL Sub Queries

Answer 2

I think what you want is count of distinct jobs for the "how many jobs" on a given date. With respect to your count and summation, I've added those too, just keep the single "group by" of the net_date.

select 
      net_date,
      count( distinct jobs ) as NumberOfJobs,
      sum( jobs ) as SumOfJobs,
      max( jobs ) as MaxJobs
   from
      net_report
   group by
      net_date

If you are looking for the date that has the MOST unique jobs going on, then add the following to the above query

  order by 2 DESC limit 1

The order by 2 refers to the ordinal column "NumberOfJobs", Limit 1 to return only the first entry of the final "ordered" result set.