Get count on a query? 1

[bminaeff]

Hello all,

I have a query that I am working on and I need to add another field to it, but I am unsure how to do it. Here is my current working query. I need to add in a count of CartonID's where the CartonID = '?????????'. Any ideas how to do this?

SELECT ContNum, DoorNum, RecDate,COUNT(CartonID) as total
FROM Details
WHERE ContNum = 'Truck_123'
GROUP BY ContNum, DoorNum, RecDate

 

[PWise]

SELECT ContNum, DoorNum, RecDate,COUNT(CartonID) as total
FROM Details
WHERE ContNum = 'Truck_123'
and CartonID = '?????????'
GROUP BY ContNum, DoorNum, RecDate and CartonID

 

[SQLSister]

The Case statment is your friend for this.

something like:

SELECT ContNum, DoorNum, RecDate,COUNT(CartonID) as total,
count(case when cartonid = '?????????' then 1 else 0 end) as Totalunknown
FROM Details
WHERE ContNum = 'Truck_123'
GROUP BY ContNum, DoorNum, RecDate

"NOTHING is more important in a database than integrity." ESquared

 

[bminaeff]

SQLSister has the right idea, I need the total count and the number of CartonID's that are just question marks. However, my query always returns with the Totalunknown being the same as the total and this isnt true. Any idea why?

 

[bminaeff]

SELECT ContNum, DoorNum, RecDate,COUNT(CartonID) as total,SUM(CASE WHEN cartonid = '???????????????????' THEN 1 ELSE 0 END) as Totalunknown
FROM Details
WHERE ContNum = 'Truck_123' 
GROUP BY ContNum, DoorNum, RecDate

This seems to work. Thanks SQLSister

 

[SQLSister]

try this to see what the problem might be

select cartonID, CASE WHEN cartonid = '???????????????????' THEN 1 ELSE 0 END
from details

That should show you the raw data (If it is a big table you might want to restrict it with a top 1000 or a where clause)

If you do this and see some unexpected results and you can't figure out why the case is returning them then post those results and we'll see if we can figure it out.

You might also consider using cartonId like '%?%' so that it will capture any number of questionmarks.

"NOTHING is more important in a database than integrity." ESquared

 

[bminaeff]

Changing out COUNT for SUM seems to do the trick. So my query now looks like this:

Declare @Status Char(1)
SELECT @Status = Status FROM ContData WHERE TruckNum = 'Truck_123'
SELECT ContNum, @Status, DoorNum, RecDate,SUM(CASE WHEN cartonid <> '???????????????????' THEN 1 ELSE 0 END) as GoodReads,SUM(CASE WHEN cartonid = '???????????????????' THEN 1 ELSE 0 END) as NoReads, COUNT(CartonID) AS Total
FROM Details
WHERE ContNum = 'Truck_123' 
GROUP BY ContNum, DoorNum, RecDate

which works but my results yield a Status column with no name. Any idea how to change that?

 

[SQLSister]

Add an alias like @status as Status

"NOTHING is more important in a database than integrity." ESquared