CODEWITHSUNDEEP
sqlsql-server
Matt M
Matt M

Reputation: 5

How to match exact column value that will vary

Using SQL I need to query a table that has a column of stock items identified via a combination of alpha and numeric code. Each of these is in a seperate column, so I could have multiples of same or different alpha codes and also corresponding numerics. In turn these are linked to a client order table.

What's the best way to query and return results where all stock items per client order have the same alpha code?

The stockitem alpha values will vary from one order to another so there is no static value that I am matching to. I'm thinking I would need to validate the first stockitemalpha value found and match it against all remaining stockitemalpha's found linked to specific clientorderid?

co.clientordersreference    | si.stockitemalpha
------------------------------------------------
1234                        | aaa
1234                        | aaa
1234                        | bbb
4343                        | ccc
4343                        | ccc
5454                        | ddd
5454                        | mmm

Data is returning as:

co.clientordersreference    | si.stockitemalpha |  stockitemalphacount 
-----------------------------------------------------------------------
1234                        | aaa               |  1
1234                        | bbb               |  1
4343                        | ccc               |  1
5454                        | ddd               |  1
5454                        | ccc               |  1
5454                        | mmm               |  1

whereas I'm expecting a result where the count is applied per clientorderreference, eg:

co.clientordersreference    | stockitemalphacount
------------------------------------------------
1234                        | 2
4343                        | 1
5454                        | 3

My initial query is along the the lines of:

Select co.clientordersreference, si.stockaitemalpha

from clientorders co, stockitem si
where co.id = si.id
and distinct(si.stockitemalpha) -- this is criteria I'm unsure of

group by co.clientordersreference

Upvotes: 0

Views: 54

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

Based on your updated question, you can try aggregating over client orders and taking the distinct count of stock items.

SELECT
    co.clientordersreference,
    COUNT(DISTINCT si.stockitemalpha) AS COUNT(DISTINCT si.stockitemalpha)
FROM clientorders co
INNER JOIN stockitem si
    ON co.id = si.id
GROUP BY
    co.clientordersreference

Upvotes: 1

Related Questions