SUM records that match criteria

[RJL1]

I have a table that has a container type (in this case) PL. I need to count the records where the container type = 'PL'. I have tries CASE and a straight count but a I get 3 records instead of 1 since only one record has the containr type of pallet.

SELECT
COUNT(
CASE
  WHEN SC.CONTAINER_TYPE = 'PL' THEN 1 ELSE 0
END) AS 'PALLETS'
FROM
SHIPPING_CONTAINER SC WITH(NOLOCK)
WHERE
SC.INTERNAL_SHIPMENT_NUM = 121386

For the example belwo i soulf only get 1 as the result

TREE_UNIT	CONTAINER_ID	SHIPMENT_ID	CONTAINER_TYPE
8026942  	00080269427C	33787	        PL
8026942 	                33787	        -
8026942 	                33787	        -

What would be the best way to count the records with container type = PL. Any help with this is apreciated

Thanks in advance
RJL

 

[sds814]

can't you do:

SELECT COUNT(SC.CONTAINER_TYPE) AS 'PALLETS'
FROM
SHIPPING_CONTAINER SC WITH (NOLOCK)
WHERE SC.INTERNAL_SHIPMENT_NUM = 121386
   AND SC.CONTAINER_TYPE = 'PL'

 

[gmmastros]

Ah.....

[tt][blue]
[!]COUNT[/!](
CASE
WHEN SC.CONTAINER_TYPE = 'PL' THEN 1 ELSE 0
END) AS 'PALLETS'
[/blue][/tt]

When you use count, you are getting a [!]row[/!] count where the data is not null. In your query, you are returning either a 1 or a zero, but never null.

There are 2 ways to fix this problem.

COUNT(
CASE
  WHEN SC.CONTAINER_TYPE = 'PL' THEN 1 ELSE [!]NULL[/!]
END) AS 'PALLETS

Make sure you return null (in the case/when) for things you don't want to count.

[!]SUM[/!](
CASE
  WHEN SC.CONTAINER_TYPE = 'PL' THEN 1 ELSE 0
END) AS 'PALLETS

Summing 1 for each item is also the same as counting them.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RJL1]

Thanks, I guess I did not put much information. here isthe rest of the problem. I can get the answer by sds814 to work just fine however in the compelte query I get some strange results

-----------------------------------------------------------
-- DAILY STATISTICS
-----------------------------------------------------------
DECLARE @STARTDATE   DATETIME
DECLARE @ENDDATE     DATETIME

SET @STARTDATE = '03/01/2011'
SET @ENDDATE   = '03/01/2011' 

SELECT
------------------- SHIPMENT HEADER  -----------------------
'ACTUAL_SHIP_DATE_TIME' = CONVERT (VARCHAR(30),SH.ACTUAL_SHIP_DATE_TIME,101),

---------------------- SECTION 01 --------------------------
'ORDERS'  = COUNT(DISTINCT SH.INTERNAL_SHIPMENT_NUM),

'LINES'   = COUNT(DISTINCT SD.INTERNAL_SHIPMENT_LINE_NUM),

'EACHES'  = SUM(SC.QUANTITY),

'PALLETS' = 
------------------------------------------------------------
FROM
SHIPMENT_HEADER SH WITH(NOLOCK)

  LEFT OUTER JOIN SHIPMENT_DETAIL SD WITH(NOLOCK)
    ON SD.INTERNAL_SHIPMENT_NUM = SH.INTERNAL_SHIPMENT_NUM

  LEFT OUTER JOIN SHIPPING_CONTAINER SC WITH(NOLOCK)
    ON SC.INTERNAL_SHIPMENT_LINE_NUM = SD.INTERNAL_SHIPMENT_LINE_NUM
------------------------------------------------------------
WHERE
SH.ACTUAL_SHIP_DATE_TIME IS NOT NULL
AND SH.COMPANY = '317'
AND SH.WAREHOUSE = '029'
AND CONVERT (VARCHAR(30),SH.ACTUAL_SHIP_DATE_TIME,101)
    BETWEEN @STARTDATE AND @ENDDATE
------------------------------------------------------------
GROUP BY
DATEPART(WEEKDAY,SH.ACTUAL_SHIP_DATE_TIME),
CONVERT (VARCHAR(30),SH.ACTUAL_SHIP_DATE_TIME,101)
ORDER BY
CONVERT (VARCHAR(30),SH.ACTUAL_SHIP_DATE_TIME,101)

The above query returns 50 as the pallet count which is wrong

SHIP_DATE	ORDERS	LINES	EACHES	PALLETS
03/01/2011	23	50	117	50

The right answers is 11 whic his the toal number of container with type PL for the date range indicated.

SHIP_DATE	ORDERS	LINES	EACHES	PALLETS
03/01/2011	23	50	117	11

I tried the last two suggestions but I get a 0 either way

SUM(CASE  WHEN SC.CONTAINER_TYPE = 'PL' THEN 1 ELSE 0 END) AS 'PALLETS'

COUNT(CASE  WHEN SC.CONTAINER_TYPE = 'PL' THEN 1 ELSE NULL END) AS 'PALLETS

Thanks for nay help with this
RJL

 

[RJL1]

After some playing with it I got it to work. Thanks again gmmastros

U R Da Man

RJL

 

[gmmastros]

try this:

-----------------------------------------------------------
-- DAILY STATISTICS
-----------------------------------------------------------
DECLARE @STARTDATE   DATETIME
DECLARE @ENDDATE     DATETIME

SET @STARTDATE = '03/01/2011'
SET @ENDDATE   = '03/01/2011' 

SELECT
------------------- SHIPMENT HEADER  -----------------------
'ACTUAL_SHIP_DATE_TIME' = CONVERT (VARCHAR(30),SH.ACTUAL_SHIP_DATE_TIME,101),

---------------------- SECTION 01 --------------------------
'ORDERS'  = COUNT(DISTINCT SH.INTERNAL_SHIPMENT_NUM),

'LINES'   = COUNT(DISTINCT SD.INTERNAL_SHIPMENT_LINE_NUM),

'EACHES'  = SC.Eaches,

'PALLETS' = SC.Pallets
------------------------------------------------------------
FROM
SHIPMENT_HEADER SH WITH(NOLOCK)

  LEFT OUTER JOIN SHIPMENT_DETAIL SD WITH(NOLOCK)
    ON SD.INTERNAL_SHIPMENT_NUM = SH.INTERNAL_SHIPMENT_NUM

  LEFT OUTER JOIN
	(Select INTERNAL_SHIPMENT_LINE_NUM,
	        SUM(SC.QUANTITY) As Eaches,
	        COUNT(CASE  WHEN SC.CONTAINER_TYPE = 'PL' THEN 1 ELSE NULL END) AS Pallets
	 From   SHIPPING_CONTAINER SC WITH(NOLOCK)
	 Group BY INTERNAL_SHIPMENT_LINE_NUM) As SC
    ON SC.INTERNAL_SHIPMENT_LINE_NUM = SD.INTERNAL_SHIPMENT_LINE_NUM
------------------------------------------------------------
WHERE
SH.ACTUAL_SHIP_DATE_TIME IS NOT NULL
AND SH.COMPANY = '317'
AND SH.WAREHOUSE = '029'
AND CONVERT (VARCHAR(30),SH.ACTUAL_SHIP_DATE_TIME,101)
    BETWEEN @STARTDATE AND @ENDDATE
------------------------------------------------------------
GROUP BY
DATEPART(WEEKDAY,SH.ACTUAL_SHIP_DATE_TIME),
CONVERT (VARCHAR(30),SH.ACTUAL_SHIP_DATE_TIME,101)
ORDER BY
CONVERT (VARCHAR(30),SH.ACTUAL_SHIP_DATE_TIME,101)

If this works, and you want me to explain it, please let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Since you got it to work (which is good), could you do me a favor and run your query and also run the one I just posted. They should come up with the same results. If they do, can you tell me which one executes faster? I'm just curious.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RJL1]

Thanks for the help. I ran your last posted script and for the EACHES it returns the right values, I had to change it from

'EACHES'  = SC.EACHES

to 

'EACHES'  = SUM(SC.EACHES)

However the pallets still calculating weird

For 3/1/2011 I should get 11 but I get 50. I tried adding the SUM or COUNT to the SC.PALLET field if not it asks me to put in the GROUP BY section and I get 2 records one with pallet qty and one with NULL in the SC.PALLET field.

Runs really fast no performace issues

Thanks
RJL