Thread: Sql group by

I have a field with the following options: DEMAND, SUPPLY, SUPPLYP, SUPPLYF. Is there a way to GROUP BY all values that contain SUPPLY in my SQL?

Current SQL is:

Code:

strSQL = "SELECT mrp_part, SUM(mrp_qty) AS mrp_qty_due, mrp_due_date, mrp_type " & _
	"FROM mrp_det " & _
	"WHERE mrp_part = '1140016058' " & _
	"GROUP BY mrp_part, mrp_due_date, mrp_type " & _
	"ORDER BY mrp_part, mrp_type, mrp_due_date"

The field that contains these values is mrp_type.

Originally Posted by BLaaaaaaaaaarche

I have a field with the following options: DEMAND, SUPPLY, SUPPLYP, SUPPLYF. Is there a way to GROUP BY all values that contain SUPPLY in my SQL?

Current SQL is:

Code:

strSQL = "SELECT mrp_part, SUM(mrp_qty) AS mrp_qty_due, mrp_due_date, mrp_type " & _
    "FROM mrp_det " & _
    "WHERE mrp_part = '1140016058' " & _
    "GROUP BY mrp_part, mrp_due_date, mrp_type " & _
    "ORDER BY mrp_part, mrp_type, mrp_due_date"

The field that contains these values is mrp_type.

Hmm...a little tricky. Try this:

Code:

SELECT
  (CASE
    WHEN mrp_type LIKE 'Supply%' THEN 'Supply'
    WHEN mrp_type LIKE 'Demand%' THEN 'Demand'
    ELSE 'Other'
  END) AS mrpType,
  mrp_part, 
  SUM(mrp_qty) AS mrp_qty_due, 
  mrp_due_date
FROM
  mrp_det
GROUP BY
  mrp_part, mrp_due_date, mrpType
ORDER BY
  mrp_part, mrpType, mrp_due_date;

Not sure how well this will work or if it even returns exactly what you want...worth a shot, though.

It seems to display the field name properly, either DEMAND or SUPPLY, but it does not perform the SUM function properly on the GROUP BY mrp_type. For instance, I have two records:

Code:

SUPPLYP - 2/18/09 - 10
SUPPLY - 2/18/09 - 45

It is currently showing those as two separate lines, rather than adding them together. Any ideas?

In JMH's SQL it groups by the alias he creates mrpType, not the field mrp_type.

Just checking this is right in your code.

It is. I needed to also add a CASE to the GROUP BY field:

Code:

	strSQL = "SELECT " & _
		"(CASE " & _
			"WHEN mrp_type LIKE 'SUPPLY%' THEN 'SUPPLY' " & _
			"WHEN mrp_type LIKE 'DEMAND%' THEN 'DEMAND' " & _
			"ELSE 'Other' " & _
		"END) AS mrp_type, " & _
		"mrp_part, SUM(mrp_qty) AS mrp_qty_due, mrp_due_date " & _
		"FROM mrp_det " & _
		"WHERE mrp_part = '1160068' " & _
		"GROUP BY mrp_part, mrp_due_date, " & _
		"(CASE " & _
			"WHEN mrp_type LIKE 'SUPPLY%' THEN 'SUPPLY' " & _
			"WHEN mrp_type LIKE 'DEMAND%' THEN 'DEMAND' " & _
			"ELSE 'Other' " & _
		"END) " & _
		"ORDER BY mrp_part, mrp_type, mrp_due_date"