Johannes Löfgren Johannes Löfgren - 2 years ago 88
SQL Question

Select and manipulate SQL data, DISTINCT and SUM?

Im trying to make a small report for myself to see how my much time I get inputed in my system every day.

The goal is to have my SQL to sum up the name, Total time worked and Total NG product found for one specific day.

In this order:

1.) Sort out my data for a specific 'date'. I.E 2016-06-03

2.) Present a DISTINCT value for 'operators'

3.) SUM() all time registered at this 'date' and by this 'operator' under 'total_working_time_h'

4.) SUM() all no_of_defects registered at this 'date' and by this 'operator' under 'no_of_defects'

date, operator, total_working_time_h, no_of_defects

Currently I get the data I want by using the Query below. But now I need both the DISTINCT value of the operator and the SUM of the information. Can I use sub-queries for this or should it be done by a loop? Any other hints where I can learn more about how to solve this?

If i run the DISTINCT function I don't get the opportunity to sum my data the way I try.

SELECT date, operator, total_working_time_h, no_of_defects FROM {$table_work_hours} WHERE date = '2016-06-03' "

Example of data as picture

Answer Source

Without knowing the table structure or contents, the following query is only a good guess. The bits to notice and work with are sum() and GROUP BY. Actually syntax will vary a bit depending on what RDBMS you are using.

  ,SUM(total_working_time_h)  AS total_working_time_h
  ,SUM(no_of_defects)         AS no_of_defects
 FROM {$table_work_hours}
 WHERE date = '2016-06-03'

(Take out the WHERE clause or replace it with a range of dates to get results per operator per date.)

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download