MYSQL QUERY, SQL

Question

I'm having a hard time putting some queries together.

SELECT `part_num`, COUNT(`part_num`) AS `total` 
    FROM `job_sheet` 
    WHERE `qty`!=0 AND `completion`=1 
    GROUP BY `part_num` 
    ORDER BY `total` DESC 
    LIMIT 10

This basically pulls up the most common part number and it shows how many times it appears in the 'total' variable. I also limited to the top 10 part number in this case. These parts number also have a quantity column 'qty' that shows how many parts there are:

part_num | qty 

1001     | 1000
1004     | 200 
1003     | 360 
1001     | 1000
1001     | 1000

In this case my first statement would show that part number 1001 would appear three times, item number 4 would appear once etc., etc. My issue is that I would like to add up the qty column along with my statement in order to show that item 1001 appears 3 times with a 'qty' sum of 3000.

Any ideas on how to do this ?

Jerry Coffin · Accepted Answer · 2012-11-30 04:12:57Z

3

You don't need to use DISTINCT as you are already using a GROUP BY.

To show the sum you simply need to add the field with the SUM() function.

SELECT part_num, COUNT(part_num) AS total, SUM(qty) as total_qty 
    FROM job_sheet 
    WHERE qty!=0 AND completion=1 
    GROUP BY part_num 
    ORDER BY total DESC 
    LIMIT 10;

edited Nov 30, 2012 at 4:12

Jerry Coffin

494k83 gold badges656 silver badges1.2k bronze badges

answered Nov 30, 2012 at 3:39

sn00k4h

4434 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Conde · Accepted Answer · 2012-11-30 02:59:00Z

3

Use DISTINCT

SELECT DISTINCT part_num, COUNT(part_num) AS total 
FROM job_sheet WHERE qty!=0 AND completion=1 
GROUP BY part_num 
ORDER BY total DESC 
LIMIT 10

answered Nov 30, 2012 at 2:59

John Conde

220k100 gold badges464 silver badges504 bronze badges

1 Comment

Dimitri Over a year ago

This won't work since I'm always missing the 'qty' column so I can add up the total 'qty' for every part. I need to be able to add up the 'qty' for everytime my part number appears. In the example above, for item 1001, I would need a separate column that shows the sum, which in this case would have been 1000+1000+1000 = 3000.

Ben · Accepted Answer · 2012-11-30 03:00:06Z

0

@JohnConde's answer is more efficient (+1), but just in case you wanted to do it in PHP:

$totals= array();

foreach ($data as $v) {
    isset($totals[$v['part_num']])) ?
        $totals[$v['part_num']] += $v['qty'] :
        $totals[$v['part_num']] = $v['qty'];   
}

answered Nov 30, 2012 at 3:00

Ben

57.6k50 gold badges187 silver badges229 bronze badges

1 Comment

Dimitri Over a year ago

Yea lol , PHP always works, but I really wanted to avoid using too much PHP to solve my issues. I had done it in PHP, but I find it gets too many when write classes...

Collectives™ on Stack Overflow

MYSQL QUERY, SQL

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related