|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
NEED HElP with Properly applying DISTINCT function to Count Aggregate.Hello, I need to know how exactly the distinct function should look
like in my SQL with the Count aggregate. Here is my SQL. How Exaclty should it look? Thank you so much! SELECT Left([Customer Table].[Zip/Postal Code],3) AS FSA, Sum([order table].SubTotal) AS SumOfSubTotal, Avg([order table].SubTotal) AS AvgOfSubTotal, Count([customer table].[Cust#]) AS [CountOfCust#] FROM [customer table] INNER JOIN [order table] ON [customer table].[Cust#] = [order table].[Sold to Customer] GROUP BY Left([Customer Table].[Zip/Postal Code],3) On 26 Jul 2006 13:29:59 -0700, Clint wrote:
>Hello, I need to know how exactly the distinct function should look Hi Clint,>like in my SQL with the Count aggregate. Here is my SQL. How Exaclty >should it look? Thank you so much! > >SELECT Left([Customer Table].[Zip/Postal Code],3) AS FSA, Sum([order >table].SubTotal) AS SumOfSubTotal, Avg([order table].SubTotal) AS >AvgOfSubTotal, Count([customer table].[Cust#]) AS [CountOfCust#] >FROM [customer table] INNER JOIN [order table] ON [customer >table].[Cust#] = [order table].[Sold to Customer] >GROUP BY Left([Customer Table].[Zip/Postal Code],3) Since you have included a GROUP BY clause, ther's no need to include DISTINCT as well. The GROOUP BY clause will limit the output to one row per Left([Customer Table].[Zip/Postal Code],3) anyway. -- Hugo Kornelis, SQL Server MVP Thank you for your time, but, I don't understand. I do want it to group
my postal codes by the first 3 digits, but I also want it to only count the same customer # once. This way I can see how many customers there are in each postal code. Currently a customer, within the grouped postal code, who has two purchases is counted as two seperate customers. That is not what I want. Can this be done? I think I need to use the DISTINCT function, but I can't get the sytax to work in SQL. Can it be done? If so, how? Thank you again. Hugo Kornelis escreveu: Show quote > On 26 Jul 2006 13:29:59 -0700, Clint wrote: > > >Hello, I need to know how exactly the distinct function should look > >like in my SQL with the Count aggregate. Here is my SQL. How Exaclty > >should it look? Thank you so much! > > > >SELECT Left([Customer Table].[Zip/Postal Code],3) AS FSA, Sum([order > >table].SubTotal) AS SumOfSubTotal, Avg([order table].SubTotal) AS > >AvgOfSubTotal, Count([customer table].[Cust#]) AS [CountOfCust#] > >FROM [customer table] INNER JOIN [order table] ON [customer > >table].[Cust#] = [order table].[Sold to Customer] > >GROUP BY Left([Customer Table].[Zip/Postal Code],3) > > Hi Clint, > > Since you have included a GROUP BY clause, ther's no need to include > DISTINCT as well. The GROOUP BY clause will limit the output to one row > per Left([Customer Table].[Zip/Postal Code],3) anyway. > > -- > Hugo Kornelis, SQL Server MVP Clint,
how about SELECT Left([Customer Table].[Zip/Postal Code],3) AS FSA, Sum([order table].SubTotal) AS SumOfSubTotal, Avg([order table].SubTotal) AS AvgOfSubTotal, -- added DISTINCT here: Count( DISTINCT [customer table].[Cust#]) AS [CountOfCust#] FROM [customer table] INNER JOIN [order table] ON [customer table].[Cust#] = [order table].[Sold to Customer] GROUP BY Left([Customer Table].[Zip/Postal Code],3) You need to use DISTINCT inside the COUNT. Oversimplified, it looks
like: SELECT Zip3, count(distinct Customer) as CustomerCount FROM Orders GROUP BY Zip3 Roy Harvey Beacon Falls, CT Show quote On 27 Jul 2006 08:53:55 -0700, "Clint" <clintste***@yahoo.ca> wrote: >Thank you for your time, but, I don't understand. I do want it to group >my postal codes by the first 3 digits, but I also want it to only count >the same customer # once. This way I can see how many customers there >are in each postal code. Currently a customer, within the grouped >postal code, who has two purchases is counted as two seperate >customers. That is not what I want. Can this be done? I think I need to >use the DISTINCT function, but I can't get the sytax to work in SQL. >Can it be done? If so, how? Thank you again. >Hugo Kornelis escreveu: > >> On 26 Jul 2006 13:29:59 -0700, Clint wrote: >> >> >Hello, I need to know how exactly the distinct function should look >> >like in my SQL with the Count aggregate. Here is my SQL. How Exaclty >> >should it look? Thank you so much! >> > >> >SELECT Left([Customer Table].[Zip/Postal Code],3) AS FSA, Sum([order >> >table].SubTotal) AS SumOfSubTotal, Avg([order table].SubTotal) AS >> >AvgOfSubTotal, Count([customer table].[Cust#]) AS [CountOfCust#] >> >FROM [customer table] INNER JOIN [order table] ON [customer >> >table].[Cust#] = [order table].[Sold to Customer] >> >GROUP BY Left([Customer Table].[Zip/Postal Code],3) >> >> Hi Clint, >> >> Since you have included a GROUP BY clause, ther's no need to include >> DISTINCT as well. The GROOUP BY clause will limit the output to one row >> per Left([Customer Table].[Zip/Postal Code],3) anyway. >> >> -- >> Hugo Kornelis, SQL Server MVP Thank you both for your answers. To Alexander, I have tried that exact
SQL and it gives me error messages. To Roy, I very much appreciate your answer, but oversimplified is just not working for me. I have tried the syntax in so many different ways. If you could take my Select line and duplicate it with the proper syntax exactly as it should look, I would be extra appreciative! Thank you both again. Here's hoping I can get it, Clint Roy Harvey wrote: Show quote > You need to use DISTINCT inside the COUNT. Oversimplified, it looks > like: > > SELECT Zip3, count(distinct Customer) as CustomerCount > FROM Orders > GROUP BY Zip3 > > Roy Harvey > Beacon Falls, CT > > On 27 Jul 2006 08:53:55 -0700, "Clint" <clintste***@yahoo.ca> wrote: > > >Thank you for your time, but, I don't understand. I do want it to group > >my postal codes by the first 3 digits, but I also want it to only count > >the same customer # once. This way I can see how many customers there > >are in each postal code. Currently a customer, within the grouped > >postal code, who has two purchases is counted as two seperate > >customers. That is not what I want. Can this be done? I think I need to > >use the DISTINCT function, but I can't get the sytax to work in SQL. > >Can it be done? If so, how? Thank you again. > >Hugo Kornelis escreveu: > > > >> On 26 Jul 2006 13:29:59 -0700, Clint wrote: > >> > >> >Hello, I need to know how exactly the distinct function should look > >> >like in my SQL with the Count aggregate. Here is my SQL. How Exaclty > >> >should it look? Thank you so much! > >> > > >> >SELECT Left([Customer Table].[Zip/Postal Code],3) AS FSA, Sum([order > >> >table].SubTotal) AS SumOfSubTotal, Avg([order table].SubTotal) AS > >> >AvgOfSubTotal, Count([customer table].[Cust#]) AS [CountOfCust#] > >> >FROM [customer table] INNER JOIN [order table] ON [customer > >> >table].[Cust#] = [order table].[Sold to Customer] > >> >GROUP BY Left([Customer Table].[Zip/Postal Code],3) > >> > >> Hi Clint, > >> > >> Since you have included a GROUP BY clause, ther's no need to include > >> DISTINCT as well. The GROOUP BY clause will limit the output to one row > >> per Left([Customer Table].[Zip/Postal Code],3) anyway. > >> > >> -- > >> Hugo Kornelis, SQL Server MVP On 27 Jul 2006 14:15:40 -0700, Clint wrote:
>Thank you both for your answers. To Alexander, I have tried that exact Hi Clint,>SQL and it gives me error messages. Can you show the error messages? Please use copy and paste and post the COMPLETE message, okay? > If you could take my Select line and The only thing you need to do is change>duplicate it with the proper syntax exactly as it should look, I would >be extra appreciative! Count([customer table].[Cust#]) AS [CountOfCust#] to Count(DISTINCT [customer table].[Cust#]) AS [CountOfCust#] But unless I miss something, that's EXACTLY what Alexander posted and what gave you errors. -- Hugo Kornelis, SQL Server MVP On 27 Jul 2006 14:15:40 -0700, "Clint" <clintste***@yahoo.ca> wrote: I don't think this is any different than what Alexander provided.>To Roy, I very much appreciate your >answer, but oversimplified is just not working for me. I have tried the >syntax in so many different ways. If you could take my Select line and >duplicate it with the proper syntax exactly as it should look, I would >be extra appreciative! Thank you both again. Here's hoping I can get >it, Clint SELECT Left([Customer Table].[Zip/Postal Code],3) AS FSA, Sum([order table].SubTotal) AS SumOfSubTotal, Avg([order table].SubTotal) AS AvgOfSubTotal, Count(DISTINCT [customer table].[Cust#]) AS [CountOfCust#] FROM [customer table] INNER JOIN [order table] ON [customer table].[Cust#] = [order table].[Sold to Customer] GROUP BY Left([Customer Table].[Zip/Postal Code],3) Roy Thanks for your time. I did do the expression exactly as suggested and
this was the error: Syntax error (missing operator) in query expression 'Count(Distinct[Customer Table].[Cust#]. Hope that helps, thanks again for your time. Clint Friesen Roy Harvey wrote: Show quote > On 27 Jul 2006 14:15:40 -0700, "Clint" <clintste***@yahoo.ca> wrote: > > >To Roy, I very much appreciate your > >answer, but oversimplified is just not working for me. I have tried the > >syntax in so many different ways. If you could take my Select line and > >duplicate it with the proper syntax exactly as it should look, I would > >be extra appreciative! Thank you both again. Here's hoping I can get > >it, Clint > > I don't think this is any different than what Alexander provided. > > SELECT Left([Customer Table].[Zip/Postal Code],3) AS FSA, > Sum([order table].SubTotal) AS SumOfSubTotal, > Avg([order table].SubTotal) AS AvgOfSubTotal, > Count(DISTINCT [customer table].[Cust#]) AS [CountOfCust#] > FROM [customer table] > INNER JOIN [order table] > ON [customer table].[Cust#] = [order table].[Sold to Customer] > GROUP BY Left([Customer Table].[Zip/Postal Code],3) > > Roy The syntax of what I posted checks out under SQL Server 2000 and 2005.
I do not know what the problem is. Just to confirm the obvious, you are using Microsoft SQL Server, aren't you? Roy Show quote On 27 Jul 2006 20:43:49 -0700, "Clint" <clintste***@yahoo.ca> wrote: >Thanks for your time. I did do the expression exactly as suggested and >this was the error: Syntax error (missing operator) in query >expression 'Count(Distinct[Customer Table].[Cust#]. Hope that helps, >thanks again for your time. Clint Friesen > >Roy Harvey wrote: >> On 27 Jul 2006 14:15:40 -0700, "Clint" <clintste***@yahoo.ca> wrote: >> >> >To Roy, I very much appreciate your >> >answer, but oversimplified is just not working for me. I have tried the >> >syntax in so many different ways. If you could take my Select line and >> >duplicate it with the proper syntax exactly as it should look, I would >> >be extra appreciative! Thank you both again. Here's hoping I can get >> >it, Clint >> >> I don't think this is any different than what Alexander provided. >> >> SELECT Left([Customer Table].[Zip/Postal Code],3) AS FSA, >> Sum([order table].SubTotal) AS SumOfSubTotal, >> Avg([order table].SubTotal) AS AvgOfSubTotal, >> Count(DISTINCT [customer table].[Cust#]) AS [CountOfCust#] >> FROM [customer table] >> INNER JOIN [order table] >> ON [customer table].[Cust#] = [order table].[Sold to Customer] >> GROUP BY Left([Customer Table].[Zip/Postal Code],3) >> >> Roy LOL! I am using Microsoft Access 2002! Very sorry for not making that
clear. I was wondering if we were on the same page. Got any new advice. Thank you so much for your time. Clint Friesen Roy Harvey wrote: Show quote > The syntax of what I posted checks out under SQL Server 2000 and 2005. > I do not know what the problem is. Just to confirm the obvious, you > are using Microsoft SQL Server, aren't you? > > Roy > > > On 27 Jul 2006 20:43:49 -0700, "Clint" <clintste***@yahoo.ca> wrote: > > >Thanks for your time. I did do the expression exactly as suggested and > >this was the error: Syntax error (missing operator) in query > >expression 'Count(Distinct[Customer Table].[Cust#]. Hope that helps, > >thanks again for your time. Clint Friesen > > > >Roy Harvey wrote: > >> On 27 Jul 2006 14:15:40 -0700, "Clint" <clintste***@yahoo.ca> wrote: > >> > >> >To Roy, I very much appreciate your > >> >answer, but oversimplified is just not working for me. I have tried the > >> >syntax in so many different ways. If you could take my Select line and > >> >duplicate it with the proper syntax exactly as it should look, I would > >> >be extra appreciative! Thank you both again. Here's hoping I can get > >> >it, Clint > >> > >> I don't think this is any different than what Alexander provided. > >> > >> SELECT Left([Customer Table].[Zip/Postal Code],3) AS FSA, > >> Sum([order table].SubTotal) AS SumOfSubTotal, > >> Avg([order table].SubTotal) AS AvgOfSubTotal, > >> Count(DISTINCT [customer table].[Cust#]) AS [CountOfCust#] > >> FROM [customer table] > >> INNER JOIN [order table] > >> ON [customer table].[Cust#] = [order table].[Sold to Customer] > >> GROUP BY Left([Customer Table].[Zip/Postal Code],3) > >> > >> Roy On 28 Jul 2006 08:19:33 -0700, "Clint" <clintste***@yahoo.ca> wrote: I hope you realize by now that this group is for discussing Microsoft>LOL! I am using Microsoft Access 2002! Very sorry for not making that >clear. I was wondering if we were on the same page. Got any new advice. >Thank you so much for your time. Clint Friesen SQL Server, not Access. There are plenty of groups devoted to Access. http://groups.google.com/groups/dir?&sel=33606877&expand=1 Roy I apologize. I went to google, typed the question I needed answered and
this is the option that came up, I took it. Thank you for your time and effort anyway. Have a great day. Roy Harvey wrote: Show quote > On 28 Jul 2006 08:19:33 -0700, "Clint" <clintste***@yahoo.ca> wrote: > > >LOL! I am using Microsoft Access 2002! Very sorry for not making that > >clear. I was wondering if we were on the same page. Got any new advice. > >Thank you so much for your time. Clint Friesen > > I hope you realize by now that this group is for discussing Microsoft > SQL Server, not Access. There are plenty of groups devoted to Access. > > http://groups.google.com/groups/dir?&sel=33606877&expand=1 > > Roy  |
|||||||||||||||||||||||
Other interesting topics