HELP WITH GROUP BY ALIAS PLEASE 1

[Smeat]

Hi
I have the following sql where I am trying to get the number of records created on each day regardless of the time it was created:

SELECT

CAST
(
	(
		STR ( YEAR ( DateCreated ) ) + '/' +
		STR ( MONTH ( DateCreated ) ) + '/' +
		STR ( DAY ( DateCreated ) )
	)
	AS DATETIME
)
AS MyAlias,

COUNT(*) AS Total 
FROM LMS_ManufacturerPendingLead
WHERE DateCreated BETWEEN '01/15/2007 16:22:56' AND '01/16/2008 09:12:16' 

GROUP BY DateCreated

this works fine but gives multiple records for the same date as the time is actually different in the underlying data, see below:

2007-09-05 00:00:00.000	1
2007-09-06 00:00:00.000	1
2007-09-06 00:00:00.000	1
2007-09-06 00:00:00.000	2
2007-09-06 00:00:00.000	1
2007-09-06 00:00:00.000	1
2007-09-07 00:00:00.000	1
2007-09-11 00:00:00.000	1

If I try to group by the alias I get the following error:

Msg 207, Level 16, State 1, Line 18
Invalid column name 'MyAlias'.

Can anyone tell me how I can group by the alias so that I get a sum of all records on each date regardless of the time portion of the field please?

i.e. There would only be 1 record for the 2007-09-06 which would have a total of 6.

TIA

Smeat

 

[hmckillop]

Try using

GROUP BY 1

i.e. 1 represents the first return field.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[hmckillop]

Sorry remove my last post, I am half asleep - that works for order by not group by

"I'm living so far beyond my income that we may almost be said to be living apart

 

[ca8msm]

You have to group by the actual code to create the alias, not the alias itself.

As an example:

[COLOR=green]-- create the table
[/color][COLOR=blue]create[/color] [COLOR=blue]table[/color] #mydates (mydate [COLOR=#FF00FF]datetime[/color])

[COLOR=green]-- insert some samle data
[/color][COLOR=blue]insert[/color] #mydates [COLOR=blue]values[/color] ([COLOR=red]'2007-09-05 00:00:00.000'[/color])
[COLOR=blue]insert[/color] #mydates [COLOR=blue]values[/color] ([COLOR=red]'2007-09-05 01:00:00.000'[/color])
[COLOR=blue]insert[/color] #mydates [COLOR=blue]values[/color] ([COLOR=red]'2007-09-05 02:00:00.000'[/color])
[COLOR=blue]insert[/color] #mydates [COLOR=blue]values[/color] ([COLOR=red]'2007-09-06 00:00:00.000'[/color])
[COLOR=blue]insert[/color] #mydates [COLOR=blue]values[/color] ([COLOR=red]'2007-09-07 00:00:00.000'[/color])
[COLOR=blue]insert[/color] #mydates [COLOR=blue]values[/color] ([COLOR=red]'2007-09-08 00:00:00.000'[/color])

[COLOR=green]-- group by date, ignoring time
[/color][COLOR=blue]select[/color] [COLOR=#FF00FF]convert[/color]([COLOR=blue]varchar[/color], mydate, 102), [COLOR=#FF00FF]count[/color](*)
[COLOR=blue]from[/color] #mydates
[COLOR=blue]group[/color] [COLOR=blue]by[/color] [COLOR=#FF00FF]convert[/color]([COLOR=blue]varchar[/color], mydate, 102)
 
[COLOR=green]-- drop the table
[/color][COLOR=blue]drop[/color] [COLOR=blue]table[/color] #mydates

-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[r937]

push the alias down into an inline view

SELECT MyAlias
     , COUNT(*) AS Total 
  FROM (
       SELECT [i]expression[/i] AS MyAlias
         FROM LMS_ManufacturerPendingLead
        WHERE DateCreated BETWEEN '01/15/2007 16:22:56' 
                              AND '01/16/2008 09:12:16' 
       ) AS inline_view
GROUP 
    BY MyAlias

however, please note that DateCreated appears to be a datetime column already, so there is no need to re-cast it as datetime

unless i'm missing something...

r937.com | rudy.ca

 

[Smeat]

Brilliant, I adopted ca8msm's solution as follows:

SELECT
CAST
(
	(
		CONVERT(varchar, DateCreated, 102)
	)
	AS DATETIME
) AS CreationDate, COUNT(*) AS Total
FROM LMS_ManufacturerPendingLead
WHERE DateCreated BETWEEN '01/15/2007 16:22:56' AND '01/16/2008 09:12:16' 
GROUP BY CONVERT(varchar, DateCreated, 102)

This does exactly what I needed, thanks.

r937, as ca8msm's solution worked I didn't get round to trying your solution but thanks for posting anyway, much appreciated.


DateCreated is a DateTime field but I was casting it in order to get rid of the time portion in my previous post.

Thanks again.

Smeat

 

[r937]

aha, stripping off the time portion

there's a more efficient method in point 7 of this FAQ:

http://www.tek-tips.com/faqs.cfm?fid=5842

r937.com | rudy.ca

 

[Smeat]

Thanks r937, i'll give that a try when I get back to the office.

Smeat