Counting

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table like this

Invoice No Container Number Referenc
03-12-767 MSKU1234567 HO0236
03-12-767 MSKU1234567 TY0359
03-12-767 GSTU5823584 OD591
03-12-767 GSTU5823584 GR569
03-12-767 GSTU5823584 IT159

How do I get a query with these outcom
Invoice No Container Number Container Coun
03-12-767 MSKU1234567
03-12-767 GSTU5823584

Best Regards
Karen
 
Try something like

SELECT [Invoice No] , [Container Number],
Count([Reference]) As [Container Count]
FROM [Your Table]
GROUP BY [Invoice No] , [Container Number]

I hope that there was a typo in your example as the count
for GSTU5823584 should be 3.

Hope This Helps
Gerald Stanley MCSD
 
Dear Gerald

I need the count of [Container Number] group by [Invoice No]. Thus the count should be 2 for both MSKU1234567 and GSTU5823584 because there is 2 container in this invoice

Kindly advise how should I do it

Thanks & Best Regards
Kare

----- Gerald Stanley wrote: ----

Try something lik

SELECT [Invoice No] , [Container Number]
Count([Reference]) As [Container Count
FROM [Your Table
GROUP BY [Invoice No] , [Container Number

I hope that there was a typo in your example as the coun
for GSTU5823584 should be 3

Hope This Help
Gerald Stanley MCS
 
Karen.

See if this works

SELECT DISTINCT C1.[Invoice No], C1.[container Number],
C2.containerCount
FROM [Your Table] C1,
(SELECT [Invoice No], Count[container Number] AS
containerCount
FROM (SELECT DISTINCT [Invoice No], [container Number] FROM
[Your Table]) GROUP BY [Invoice No] ) C2
WHERE C1.[Invoice No] = C2.[Invoice No]

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
Dear Gerald,

I need the count of [Container Number] group by [Invoice
Click to expand...
No]. Thus the count should be 2 for both MSKU1234567 and
GSTU5823584 because there is 2 container in this invoice.
Kindly advise how should I do it.

Thanks & Best Regards,
Karen

----- Gerald Stanley wrote: -----

Try something like

SELECT [Invoice No] , [Container Number],
Count([Reference]) As [Container Count]
FROM [Your Table]
GROUP BY [Invoice No] , [Container Number]

I hope that there was a typo in your example as the count
for GSTU5823584 should be 3.

Hope This Helps
Gerald Stanley MCSD
-----Original Message-----
I have a table like this:
03-12-767 MSKU1234567 HO02367
03-12-767 MSKU1234567 TY03592
03-12-767 GSTU5823584 OD5912
03-12-767 GSTU5823584 GR5692
03-12-767 GSTU5823584 IT1593
Invoice No Container Number Container Count
03-12-767 MSKU1234567 2
03-12-767 GSTU5823584 2
Karen
.
Click to expand...
.
Click to expand...
 
Back
Top