April 11, 2008 at 10:16 am
Can anyone tell me whether its possible to put the following 3 statements into 1 bigger statement?
**The following query is to ensure only unique email addresses are selected and no records are **selected from the excludes file
select max(surname) as surname
,max(postcode) as postcode
,max(emailaddress) as emailaddress
,max(startdate) as startdate
into Name_Address_Email
from Master
where ID not in (select ID from Master_Excludes)
group by EmailAddress
order by EmailAddress
**The next query is to ensure there is only 1 record per Postcode
select max(surname) as surname
,max(postcode) as postcode
,max(emailaddress) as emailaddress
,max(startdate) as startdate
into Name_Address_Email2
from Name_Address_Email
group by Postcode
order by Postcode
**The next query is to break down the results by month
select substring(StartDate,6,2)
,count(*)
from Name_Address_Email2
group by substring(StartDate,6,2)
order by substring(StartDate,6,2)
Any help with this would be great!
Thanks
Bicky1980
April 11, 2008 at 11:10 am
Hi Bicky1980,
The short answer is yes... But before hand another question is "what is the end result you are trying to achieve?" The result of your three queries will simply give you a count of unique postcodes that have an emailaddress, split into the 12 months. It will not account for year either.
It looks like the dates are stored as strings in the format YYYY-MM-DD, this can be cast to a datetime and then you can use the YEAR and MONTH functions to group the data.
A couple of other points that you need to be aware of; the column(s) that you are grouping by do not need to be in aggregate functions as there will only ever be one result per row.
Also, you need to be careful when using aggregate functions. In your example, grouping by emailaddress and selecting max(surname) and max(start_date) will 'unlink' this data, in that you will receive the most recent date and the surname furthest down the alphabetical order associated with that email address, not necessarily the start_date of the surname. If you know what I mean.
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
April 14, 2008 at 2:29 am
Hi Rod
If I was not to aggregate my fields it won't allow me to select them fields as they are not part of my group - How do you suggest this should be done?
Thanks in advance
Bicky1980
April 14, 2008 at 6:36 am
bicky1980 (4/14/2008)
Hi RodIf I was not to aggregate my fields it won't allow me to select them fields as they are not part of my group - How do you suggest this should be done?
Thanks in advance
Bicky1980
I was referring to the fields that are part of your group.
What is the final result that you want to get out?
Oh, and it's Rob by the way! 😉
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
April 14, 2008 at 6:45 am
I'm sure they can be combined, but I need to know more about what the desired end result of the query is. For one thing, it's not clear to me what postal codes have to do with e-mail addresses.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 14, 2008 at 6:54 am
I am trying to produce a breakdown of the start months of customers who have a unique mailing address using the field postcode (postcode - is my unique address key) and also have a unique email address - I don't want to count any records where there are dupliacate addresses or email addresses
Hope this explains
Thanks
Bicky1980
p.s. Sorry Rob...:P
April 15, 2008 at 7:11 am
Since you say postcode is your unique key, you can safely assume that there will not be duplicates in the table without having to group by the column.
If you still have duplicate email addresses in the table then that means that they will be registered at different postcodes and do you really want to filter them out? (For instance, you have 10 records at various postcodes, all with the email address 'billy.smart@circus.com', which record do you keep)
Lastly, are you wanting to group solely on month, so all records are summarised into 12 rows? eg All Januaries over a 5 years span are counted together.
If all this is true, then the answer can be as simple as
SELECT
MONTH(CAST(StartDate as DateTime)) as StartMonth
, Count(*) as CustomerCount
FROM
Master
WHERE
ID NOT IN (SELECT ID FROM Master_Excludes)
GROUP BY
MONTH(CAST(StartDate as DateTime))
And if you REALLY want to only count unique email addresses as well, then this should be sufficient:
SELECT
MONTH(CAST(StartDate as DateTime)) as StartMonth
, Count(DISTINCT emailaddress) as CustomerCount
FROM
Master
WHERE
ID NOT IN (SELECT ID FROM Master_Excludes)
GROUP BY
MONTH(CAST(StartDate as DateTime))
--------
[font="Tahoma"]I love deadlines. I like the whooshing sound they make as they fly by. -Douglas Adams[/font]
April 29, 2008 at 8:21 am
Hello Rob,
Only just had chance to look at this problem again...I see what you are saying however...
When I say the postcode is my unique address key - I meant I only want one instance of each address key - There are several with the same address key....
Thanks
Bicky
:hehe:
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply