Geek asks and answers

SQL Query Help - Highest Sum Field Required

I have a NET_REPORT table 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 several records for one NET_DATE, each with a different code.
I need to know the maximum number of jobs in one NET_DATE for all codes.

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

+3
source share
3 answers

Simple version

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

If you want to order NET_DATE, starting with the highest value

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

NET_DATE

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


+2
SELECT MAX(JobsTotal) as MaximumJobs
FROM (
    SELECT SUM(JOBS) AS JobsTotal
    FROM NET_REPORT
    GROUP BY NET_DATE
) A
+1

I think you need to calculate the number of tasks for the "number of tasks" for a specific date. As for your counting and summing, I added them too, just save the single “group” from 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 a date with unique MOST jobs, add the following to the above query

  order by 2 DESC limit 1

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

+1
source

More articles:


All Articles