How to count this field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to count this field

Author

Topic

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-03-05 : 11:55:03

I have a table like this. Each ConNo can have many boxes. The number of the box is an identfier, not a quantity.ie Box 3 = 1 box.A box may have a letter as a suffix or not.

I want to group by ConNo and total the boxes so I know how many Boxes are in each ConNo eg ConNo 200 5 boxes

TransId 1234
ConNo 200
BoxNo 1
BoxSuffix

TransId 1235
ConNo 200
BoxNo 1
BoxSuffix

TransId 1236
ConNo 200
BoxNo 2
BoxSuffix

TransId 1237
ConNo 200
BoxNo 2
BoxSuffix A

ConNo 200

BoxNo 1

TransId 1238
ConNo 201
BoxNo 1

TransId 1239
ConNo 201
BoxNo 1

TransId 1240
ConNo 201
BoxNo 3

TIA

mcupples
Starting Member

9 Posts

Posted - 2007-03-05 : 12:04:30

a "select count([fieldname])" may work?

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-03-06 : 05:09:45

Thanks. I have this code now which runs but returns results like

1 0
1 0
1 0...

SELECT ConNo, COUNT(DISTINCT CAST(BoxNo AS varchar) + BoxSuffix) AS Expr3
FROM tblTransmittalDetails
GROUP BY ConNo, BoxNo, BoxSuffix
ORDER BY ConNo

This works fine, but the Box Suffix isn't concatenated to BoxNo

SELECT ConNo, COUNT(DISTINCT BoxNo) AS Expr1
FROM tblTransmittalDetails
GROUP BY ConNo
ORDER BY ConNo, COUNT(BoxNo)

It returns what I want but with out the suffix. Box1 abd Box1A must count as 2 boxes...
1 39
2 8
3 25.....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 05:27:03

Add sample data in proper format...

DECLARE @Data TABLE (...)
INSERT @Data
SELECT x, y, z UNION ALL
SELECT a, b, c

and the you post your expected output.

Peter Larsson
Helsingborg, Sweden

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-03-06 : 05:53:01

Sorry - you've lost me. I want to do this as a view, not an sp......I thought I'd done what you asked.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-06 : 05:57:56

Maybe this?

SELECT ConNo, COUNT(DISTINCT BoxNo) AS Expr1
FROM tblTransmittalDetails
GROUP BY ConNo, BoxSuffix
ORDER BY 1, 2

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-06 : 06:10:33

No, I meant like this

-- Prepare sample data
DECLARE	@Data TABLE (TransID INT, ConNo INT, BoxNo INT, BoxSuffix VARCHAR(20))

INSERT	@Data
SELECT	1234, 200, 1, NULL UNION ALL
SELECT	1235, 200, 1, NULL UNION ALL
SELECT	1236, 200, 2, NULL UNION ALL
SELECT	1237, 200, 2, 'A' UNION ALL
SELECT	1238, 201, 1, NULL UNION ALL
SELECT	1239, 201, 1, NULL UNION ALL
SELECT	1240, 201, 3, NULL

And then we can post solutions/suggestions like this

-- First try
SELECT		ConNo,
		COUNT(*) 
FROM		@Data
GROUP BY	ConNo

-- Second try
SELECT		ConNo,
		COUNT(*) 
FROM		(
			SELECT DISTINCT	ConNo,
					BoxNo,
					BoxSuffix
			FROM		@Data
		) AS x
GROUP BY	ConNo
ORDER BY	ConNo

Peter Larsson
Helsingborg, Sweden

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-03-06 : 06:19:33

Thank you Harsh Athalye. That works in a fashion !

An example result is this where Con No 2444 has 12 boxes in total. 10 are just numbers and two are 8A and 8B, so you can see what it is doing. Maybe I can sum the three figures in my report as the view will be used to produce a Crystal report.

Con No Expr1

2444 1
2444 1
2444 10

Thanks for your help - I thought you maybe you were going to tell me off again ! :-)

Pinto
Aged Yak Warrior

590 Posts

Posted - 2007-03-06 : 07:02:53

I summed it in the Crystal Report - thanks for all help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources