COUNT with Group By producing two similar groups 2

[TheBugSlayer]

Hi guys.

I have a very simple query:

SELECT CASE LTRIM(RTRIM(CaseType))
			WHEN 'Surgical' THEN 'S' 
			WHEN 'Dermatology' THEN 'D'
			WHEN 'Fluorescence' THEN 'D'
			WHEN 'Non-GYN Cytology' THEN 'N'
			END AS Code, COUNT(Accession), ReceivedDate AS DateToProcess FROM PathSolutionsStaging
GROUP BY ALL CaseType, ReceivedDate

The result is:

Code TotalCases DateToProcess
D 18 12/10/2012
D 3 12/10/2012
N 8 12/10/2012
S 512 12/10/2012


but I would like to get:

Code TotalCases DateToProcess
D 21 12/10/2012
N 8 12/10/2012
S 512 12/10/2012


What's the easiest way?

Thank you.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[JohnDuncanTB]

I think changing the GROUP BY should do the trick.

SELECT CASE LTRIM(RTRIM(CaseType))
			WHEN 'Surgical' THEN 'S' 
			WHEN 'Dermatology' THEN 'D'
			WHEN 'Fluorescence' THEN 'D'
			WHEN 'Non-GYN Cytology' THEN 'N'
			END AS Code, COUNT(Accession), ReceivedDate AS DateToProcess FROM PathSolutionsStaging
GROUP BY ALL
	[s]CaseType,[/s]
	LTRIM(RTRIM(CaseType))
		WHEN 'Surgical' THEN 'S' 
		WHEN 'Dermatology' THEN 'D'
		WHEN 'Fluorescence' THEN 'D'
		WHEN 'Non-GYN Cytology' THEN 'N'
	END, ReceivedDate

 

[gmmastros]

JohnDuncanTB suggestion should work. I would have done it this way instead.

Try this:

; With Data As
(
  SELECT CASE LTRIM(RTRIM(CaseType))
			  WHEN 'Surgical' THEN 'S' 
			  WHEN 'Dermatology' THEN 'D'
			  WHEN 'Fluorescence' THEN 'D'
			  WHEN 'Non-GYN Cytology' THEN 'N'
  			  END AS Code, 
		  COUNT(Accession) As CodeCount, 
		  ReceivedDate AS DateToProcess 
  FROM    PathSolutionsStaging
  GROUP BY ALL CaseType, ReceivedDate 
)
Select Code,
       Sum(CodeCount) As TotalCases,
	   DateToProcess
From   Data
Group By Code, DateToProcess

I'm not suggesting that my method is "better", just that it's an alternative approach.

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

 

[TheBugSlayer]

Ah, thank you guys. The proverbial extra pair of eyes. The oversight is now clear thanks to your posts. My problem was the grouping. I like CTEs but I am going with John's solution to keep the query short, after correcting the omission: keyword CASE is missing.

This works:

SELECT CASE LTRIM(RTRIM(CaseType))
			WHEN 'Surgical' THEN 'S' 
			WHEN 'Dermatology' THEN 'D'
			WHEN 'Fluorescence' THEN 'D'
			WHEN 'Non-GYN Cytology' THEN 'N'
			END AS Code, COUNT(Accession), ReceivedDate AS DateToProcess FROM PathSolutionsStaging
GROUP BY [b][COLOR=red]CASE[/color][/b] LTRIM(RTRIM(CaseType))
		WHEN 'Surgical' THEN 'S' 
		WHEN 'Dermatology' THEN 'D'
		WHEN 'Fluorescence' THEN 'D'
		WHEN 'Non-GYN Cytology' THEN 'N'
	END, 
	ReceivedDate

Thank you both!

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)