SQLTeam.com | Weblogs | Forums

Getting the First Occurence


#1

Hi,

I am newbie here. I have this data, what would be the query?

Master Table

Invoice No. | Line No. | Cust. No. | Qty.

A123 | 1 | 101-5 | 15000

Manufacturing Table
Process ID | Invoice No. | Line No. | Cust. No. | Qty. | Mfg. Country

1014| A123 | 1 | 101-5 | 10000 | HK
1015| A123 | 1 | 101-5 | 5000 | NY

The result should be
Invoice No. | Line No. | Cust. No. | Qty. | Mfg Country

A123 | 1 | 101-5 | 15000 | HK


#2

Use this guideline for posting questions:

How to post SQL questions

It makes less work for those who want to help and increases your chances of good, timely answers!


#3

The desired results would be incorrect because not all the quantity would come from "HK". Either take out the Mfg. Country or continue to list both. To be honest, I won't help in the writing of code that creates incorrect results,


#4

try this

select
InvoiceNo,[Line No],SUM(qty)qty ,MIN([Mfg. Country]) [Mfg. Country]
from
ManufacturingTable
group by
InvoiceNo ,[Line No]