Need to group by

Question

I need to do a group by
SELECT 
Units.LinkNumber, 
Units.RPUnitNumber,
Forms.Type AS [Type],
Types.TypeDescription AS [Type Desc], 
Forms.FormID, 
Forms.OrderID
FROM 
Types INNER JOIN Forms ON Types.Type = Forms.Type,
 -- Get Type for each form
Units INNER JOIN Orders On ((Orders.OrderID = Units.DisposalOrderID) or (Orders.OrderID = Units.AcquisitionOrderID) )
 --- Get Orders for Units either being replaced or added, RPUnitNumber is either a replacment vehicle or addition vehicle
order by  Forms.FormID asc

I need to do a group by formid.

There is are many orderid to one formid

LinkNumber	RPUnitNumber	Type	Type Desc	FormID	OrderID
14395	1015687	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14396	1015236	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14406	1282999	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14405	1012999	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
10137	1178305	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14400	1157896	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
13476	8239946	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
10137	1178305	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14152	1610375	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14399	1618975	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
10273	1241806	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14401	5332901	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14403	4531601	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14397	1174578	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14408	1256457	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
10322	1251637	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14398	8238745	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14402	1171538	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
11084	2515202	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14404	1281234	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14410	2512500	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14417	5052221	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14415	1282888	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14411	1172601	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14412	9023586	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14416	5558201	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
12903	5547415	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
11004	2077415	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14407	1282998	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14409	1647896	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
10405	1265201	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14418	5541302	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14419	1632210	711	TRAILER COMPRESSOR AIR 185 CFM	1	4
14395	1015687	161	VAN MINI 4000 GVW CARGO 2WD	2	5
14396	1015236	161	VAN MINI 4000 GVW CARGO 2WD	2	5
14405	1012999	161	VAN MINI 4000 GVW CARGO 2WD	2	5
14406	1282999	161	VAN MINI 4000 GVW CARGO 2WD	2	5
10137	1178305	161	VAN MINI 4000 GVW CARGO 2WD	2	5
14400	1157896	161	VAN MINI 4000 GVW CARGO 2WD	2	5
10137	1178305	161	VAN MINI 4000 GVW CARGO 2WD	2	5
13476	8239946	161	VAN MINI 4000 GVW CARGO 2WD	2	5
14152	1610375	161	VAN MINI 4000 GVW CARGO 2WD	2	5
14399	1618975	161	VAN MINI 4000 GVW CARGO 2WD	2	5
14401	5332901	161	VAN MINI 4000 GVW CARGO 2WD	2	5
10273	1241806	161	VAN MINI 4000 GVW CARGO 2WD	2	5
14403	4531601	161	VAN MINI 4000 GVW CARGO 2WD	2	5

---

Mathieu Alexandre Cupryk www.omegalove.com

Answer 1

Looking at your data, it is impossible to group them because columns 1 and 2 differ in each row.
One possible solution is to remove the first two columns, if you do that then you have to group by all columns in the selection finally, the only ones that can be omitted are the aggregation function. 

Try this

SELECT 
	Forms.Type AS 'Type',
	Types.TypeDescription AS 'Type Desc', 
	Forms.FormID, 
	Forms.OrderID
FROM 
	Types INNER JOIN Forms ON Types.Type = Forms.Type,
	Units INNER JOIN Orders On ((Orders.OrderID = Units.DisposalOrderID) or (Orders.OrderID = Units.AcquisitionOrderID))
group by
	Forms.Type,
	Types.TypeDescription, 
	Forms.FormID, 
	Forms.OrderID 
order by  Forms.FormID asc

Answer 2

Hi,

Please try hope it will help you:

SELECT 
Units.LinkNumber, 
Units.RPUnitNumber,
Forms.Type AS [Type],
Types.TypeDescription AS [Type Desc], 
Forms.FormID, 
Forms.OrderID
FROM 
Types INNER JOIN Forms ON Types.Type = Forms.Type,
 -- Get Type for each form
Units INNER JOIN Orders On ((Orders.OrderID = Units.DisposalOrderID) or (Orders.OrderID = Units.AcquisitionOrderID) )
 --- Get Orders for Units either being replaced or added, RPUnitNumber is either a replacment vehicle or addition vehicle
group by
	Forms.Type,
	Types.TypeDescription, 
	Forms.FormID, 
	Forms.OrderID 
order by  Forms.FormID asc

---

Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

Answer 3

What exactly do you mean with "group by formid". Can you show us the desired results?

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 4

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules (you did not!). 

What few hints you gave us look awful. Why do you believe in a magical generic “type of nothing in particular” in your schema? It should not have its own table! It is am attribute, not an entity. Think about a “blood_type” that is not part of a person. 

A “<something>_type” will be a column in the Orders table that uses this form. The form_type (my guess, sine you did not bother with a valid data element name) might reference another table, but more likely a good design would use a CHECK() constraint. 

SELECT U.link_nbr, U.rp_unit_nbr,
       O.form_type, O.form_id, O.order_nbr
  FROM Units AS U, Orders AS O
 WHERE O.order_nbr
      IN (U.disposal_order_nbr, U.acquisition_order_nbr);

The prefixes “disposal_” and “acquisition_” are called “roles” in data modeling. They have to reference a table in which they are drawn and do not play a role. 

The “link_nbr” is very scary. The term “link” is not part of RDBMS; it refers to double-linked lists in the old network DBMS systems. Sometimes, you will see SQL programmers who are so bad that they will try to mimic these pointers with IDENTITY, GUID, and other non-relational things to fake a pointer. What does it mean? 

Want to try again and use good manners the next time? My guess is that your schema is a mess and needs to be re-done. 

---

--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

Answer 5

I have multiple orderids for each formid

so I would like to group by orders.orderid

FormID	OrderID	DivisionDepartment	DeptNumber	DeptDescription	DeptCode	DeptCodeDesc	OpLeaseAcct	CapLeaseAcct	CapContributionAcct	InternalRentalAcct	ExternalRentalAcct	ConsumablesAcct	DamagesAcct	PowerToolsAcct	FuelAcct	ProFuelAccount	ProFuelAccountN	ProFuelVerified	InsuranceAcct	BillingFrequency	MfgNonVehAcct	MfgVehAcct	PhotoRadar	CarWashAcct	SpecFeesAcct	PowerTools	ContactName	ContactTelNum	InActive	InActiveDate	NewDeptNum	CustomerSignatured	ControllerSignatured	CustomerUserName	ControllerUserName	Type	Engine	Usage	AccountNumber	Notes
14	80	PW15010806	PW15010806	PW-PARKS-NORTH	PWPKS	PW - Parks And Open Space	001-525027-181501-999	001-527011-181501-999	001-572050-181501-999	001-527021-181501-999	001-527032-181501-999	001-525028-181501-999	001-525029-181501-999	001-532091-181501-999	001-533012-181501-999	271	271	1	001-526051-181501-999	Monthly	001-536029-181501-999	001-536028-181501-999	001-149037-181501	001-536070-181501-999	001-525060-181501-999	Yes	JACK LUBINSKI	986-3116	0	NULL	NULL	NULL	NULL	NULL	NULL	312	D	L	383-181400-839101	Unit is to come with attachmments.  72" Roary Broom  72" Rotary Mower  15 Ft Tow Behind Mower
15	80	PW15010806	PW15010806	PW-PARKS-NORTH	PWPKS	PW - Parks And Open Space	001-525027-181501-999	001-527011-181501-999	001-572050-181501-999	001-527021-181501-999	001-527032-181501-999	001-525028-181501-999	001-525029-181501-999	001-532091-181501-999	001-533012-181501-999	271	271	1	001-526051-181501-999	Monthly	001-536029-181501-999	001-536028-181501-999	001-149037-181501	001-536070-181501-999	001-525060-181501-999	Yes	JACK LUBINSKI	986-3116	0	NULL	NULL	NULL	NULL	NULL	NULL	312	D	L	383-181400-839101	Unit is to come with attachmments.  72" Rotary Broom  72" Rotary Mower  15 Ft Tow Behind Mower

SELECT
	   Forms.[FormID]
      ,Forms.[OrderID]
      ,O.DivisionDepartment
      ,D.*
      ,Forms.[CustomerSignatured]
      ,Forms.[ControllerSignatured]
      ,Forms.[CustomerUserName]
      ,Forms.[ControllerUserName]
      ,Forms.[Type]
      ,Forms.[Engine]
      ,Forms.[Usage]
      ,Forms.[AccountNumber]
      ,Forms.[Notes]
  FROM [ReplacementSchedule].[dbo].[Forms]
  
  INNER join Orders O on (Forms.OrderID = O.OrderID)
  LEFT join Departments D on (O.DivisionDepartment = D.DeptNumber)
  order by Forms.FormID asc

---

Mathieu Alexandre Cupryk www.omegalove.com

Answer 6

You can group by all those columns, but you should omit FormID column (based on this sample data)

With a single column in a row has a value different from the rest, not enough to generate the grouping you want

SELECT
       Forms.[OrderID]
      ,O.DivisionDepartment
      ,D.*
      ,Forms.[CustomerSignatured]
      ,Forms.[ControllerSignatured]
      ,Forms.[CustomerUserName]
      ,Forms.[ControllerUserName]
      ,Forms.[Type]
      ,Forms.[Engine]
      ,Forms.[Usage]
      ,Forms.[AccountNumber]
      ,Forms.[Notes]
  FROM [ReplacementSchedule].[dbo].[Forms]
  
  INNER join Orders O on (Forms.OrderID = O.OrderID)
  LEFT join Departments D on (O.DivisionDepartment = D.DeptNumber)
group by 
       Forms.[OrderID]
      ,O.DivisionDepartment
      ,D.*
      ,Forms.[CustomerSignatured]
      ,Forms.[ControllerSignatured]
      ,Forms.[CustomerUserName]
      ,Forms.[ControllerUserName]
      ,Forms.[Type]
      ,Forms.[Engine]
      ,Forms.[Usage]
      ,Forms.[AccountNumber]
      ,Forms.[Notes]
order by Forms.OrderID