SQL To Count When Certain Columns In Rows Are The Same Except For A Single Column

Question

We have a table of orders that can have multiple lines per order. All lines on an order have to have the same Product Type when submitted. So, so therefore if an order that has multiple lines has differing Product Types, it is split into two or more orders.

I am using Microsoft SQL Server Mgmt Studio for querying the table. I am no expert in SQL and am having trouble coming up with a query to show the count for the number of orders that were split.

Here is an example of the way our orders are stored in our table(we'll call it dbo.OrderTable). We use Purchase_Order_Number and Account_Number as a super key.

PurchaseOrderNumber	AccountNumber	LineNumber	Quantity	UnitPrice	RequestedUOM	Style	Color	ProductType
217344	0026985	1	30	5.40	EA	hwwtg	11028	H
217344	0026985	2	10	7.99	SQF	lr105	00114	C
BD624919	0210995	1	158	12.80	SHT	551MR	00122	H
BD624920	0210995	1	12	3.80	SQY	211MX	00132	H
BD624920	0210995	2	12	0.99	EA	HA258	01088	H
1269407	1911403	1	55	12.99	BOX	HSMPR	00150	H
1269407	1911403	2	2	200.00	ROL	TGJ88	01088	C
1269407	1911403	3	1	10.00	EA	00000	00001	R

The SQL should return 2 since there are 2 orders that have different ProductTypes. Any help on this would be greatly appreciated.

shawnt00 · Accepted Answer · 2021-12-17 18:09:11Z

1

select count(*) from (
    select PurchaseOrderNumber from dbo.OrderTable
    group by PurchaseOrderNumber
    having count(distinct ProductType) > 1
) t

edited Dec 17, 2021 at 18:09

answered Dec 17, 2021 at 16:52

shawnt00

18.1k3 gold badges23 silver badges25 bronze badges

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

Comments

JNevill · Accepted Answer · 2021-12-17 16:25:01Z

1

I think something like the following should do the trick:

SELECT COUNT(distinct t1.PurchaseOrderNumber)
FROM yourtable t1
   INNER JOIN yourtable t2
       ON t1.PurchaseOrderNumber = t2.PurchaseOrderNumber
       AND t1.ProductType <> t2.ProductType

This is just self joining on the table for the same order, but different product types and then counting the number of distinct PurchaseOrderNumbers that survive the join criteria.

answered Dec 17, 2021 at 16:25

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

1 Comment

Rob Over a year ago

This is also a solution that worked for me. Thank you!

Fabio Scagliola · Accepted Answer · 2021-12-17 18:05:31Z

0

The ROW_NUMBER function should provide an overview of the number of orders required for each line

Please consider the following sample code

create table table1 (orderId nvarchar(450), orderType nchar(1))
insert table1 values 
  ('217344',   'H')
, ('217344',   'C')
, ('BD624919', 'H')
, ('BD624920', 'H')
, ('BD624920', 'H')
, ('1269407',  'H')
, ('1269407',  'C')
, ('1269407',  'R')
select orderId, orderType, row_number() over (partition by orderId order by orderType) numberOfOrders  from table1

Result

orderId	orderType	numberOfOrders
1269407	C	1
1269407	H	2
1269407	R	3
217344	C	1
217344	H	2
BD624919	H	1
BD624920	H	1
BD624920	H	2

answered Dec 17, 2021 at 18:05

Fabio Scagliola

5121 gold badge5 silver badges14 bronze badges

Collectives™ on Stack Overflow

SQL To Count When Certain Columns In Rows Are The Same Except For A Single Column

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related