GROUP BY? COUNT? help with a sort

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

GROUP BY? COUNT? help with a sort

Author

Topic

baseball43v3r`
Starting Member

8 Posts

Posted - 2011-03-09 : 16:06:59

hey guys, newbie here so please go gentle.

I have two tables, 1 with customers, and 1 with their account info.

I'm trying to show all customers with a certain name, and then their account types and then show how many of that account they have. so far i've gotten to everything but showing how many of the account of that type they have. I have tried a GROUP BY, as well as a COUNT but neither worked. my code is as follows.


SELECT Customer.Customer_ID, Last_Name + ' ' + First_Name AS 'Customer Name', Account_Type

FROM Customer

INNER JOIN Account
	ON Customer.Customer_ID = Account.Customer_ID

WHERE Last_Name = 'Rudyard' OR First_Name = 'Rudyard'

ORDER BY Customer.Customer_ID, Account_Type

and my output is as follows


Customer_ID Customer Name                                                                                         Account_Type
----------- ----------------------------------------------------------------------------------------------------- ------------
B0190102    Rudyard Margaret                                                                                      CA
B0190706    Kipling Rudyard                                                                                       CA
B0190706    Kipling Rudyard                                                                                       CD
B0190706    Kipling Rudyard                                                                                       CD
B0190706    Kipling Rudyard                                                                                       SA
R0198100    Kipling Rudyard                                                                                       CA
R0198100    Kipling Rudyard                                                                                       SA

i need to add a column beyond account type showing total number of account types for each customer ID. Can someone help me out and explain the logic behind it? is that a GROUP BY? a COUNT? and what syntacically would the code be? many thanks.
John

masterdineen
Aged Yak Warrior

550 Posts

Posted - 2011-03-09 : 17:19:25

hello there

try

SELECT Customer.Customer_ID, Last_Name + ' ' + First_Name AS 'Customer Name', Account_Type, count(acc.customerID) as 'Number of Accounts'

FROM Customer

INNER JOIN Account acc
ON Customer.Customer_ID = Account.Customer_ID

WHERE Last_Name = 'Rudyard' OR First_Name = 'Rudyard'

group BY Customer.Customer_ID, 'Customer Name', Account_Type

have a go at that, see how you get on, post any errors you receive and then we can go from there.

THis is my first time in answering questions instead of asking all the time. Take care

Regards

Rob

baseball43v3r`
Starting Member

8 Posts

Posted - 2011-03-09 : 21:50:17

hey rob thanks for the reply. i tried the following as you suggested



SELECT Customer.Customer_ID, Last_Name + ' ' + First_Name AS 'Customer Name', Account_Type, count(acc.customerID) as 'Number of Accounts'

FROM Customer

INNER JOIN Account acc
ON Customer.Customer_ID = Account.Customer_ID

WHERE Last_Name = 'Rudyard' OR First_Name = 'Rudyard'

group BY Customer.Customer_ID, 'Customer Name', Account_Type

and got the following error

quote:

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "Account.Customer_ID" could not be bound.

so i changed it to acc.Customer_ID and reran it and got the following error.

quote:

Msg 164, Level 15, State 1, Line 2
Each GROUP BY expression must contain at least one column that is not an outer reference.

which is completely lost on me. I did noticed one thing, you are trying to group by customer_ID and i should clarify i'm trying to group by Account_Type. ie account B0190706 has 2 CD accounts, so the row should read

Account_ID Name Account_Type # of Account Type
B0190706 Kipling Rudyard CD 2

If i'm reading it wrong i apologize, i'm just trying to clarify my intent

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-09 : 21:57:55

if you are using SQL 2005/2008, you can use the over() window function


SELECT Customer_ID, [Customer Name], Account_Type, 
       Total_Acc_Type = COUNT(DISTINCT Account_Type) OVER(PARTITION BY Customer_ID)
FROM
(
SELECT Customer.Customer_ID, Last_Name + ' ' + First_Name AS 'Customer Name', Account_Type
FROM   Customer
INNER JOIN Account
	ON Customer.Customer_ID = Account.Customer_ID
WHERE Last_Name = 'Rudyard' OR First_Name = 'Rudyard'
) c
ORDER BY Customer_ID, Account_Type

KH
[spoiler]Time is always against us[/spoiler]

baseball43v3r`
Starting Member

8 Posts

Posted - 2011-03-09 : 22:02:46

i get

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'distinct'.

when i try your solution khtan

Washu
Starting Member

2 Posts

Posted - 2011-03-09 : 22:07:56

From the example masterdineen provided, try this (note small change in the group by clause)...

'Customer Name' in the group by clause will not work in this case. It is not recognized. Because ACCOUNT_TYPE is listed last in the group by clause, I believe you will see the results you want.

SELECT Customer.Customer_ID, Last_Name + ' ' + First_Name AS 'Customer Name', Account_Type, count(acc.customerID) as 'Number of Accounts'

FROM Customer

INNER JOIN Account acc
ON Customer.Customer_ID = Account.Customer_ID

WHERE Last_Name = 'Rudyard' OR First_Name = 'Rudyard'

group BY Customer.Customer_ID, Last_Name, First_Name, Account_Type

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-03-09 : 22:17:51

quote:
Originally posted by baseball43v3r`

i get

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near 'distinct'.

when i try your solution khtan

hmmm... seems that the count() in over() does not support distinct.
Back to good old INNER JOIN with the aggregate method


; WITH cust AS
(
	SELECT Customer.Customer_ID, Last_Name + ' ' + First_Name AS 'Customer Name', Account_Type
	FROM   Customer
	INNER JOIN Account
		ON Customer.Customer_ID = Account.Customer_ID
	WHERE Last_Name = 'Rudyard' OR First_Name = 'Rudyard'
)
SELECT	*
FROM	cust c
	INNER JOIN
	(
		SELECT	Customer_ID, cnt = COUNT(DISTINCT Account_Type)
		FROM	cust
		GROUP BY Customer_ID
	) t	ON	c.Customer_ID	= t.Customer_ID

KH
[spoiler]Time is always against us[/spoiler]

baseball43v3r`
Starting Member

8 Posts

Posted - 2011-03-10 : 00:09:38

correction... for Washu i get...

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

and for khtan i get the following output, which is close but still slightly off as it seems to count all accounts of that id and not of the actualy account type of that id. can you explain the second half of your code a little bit khtan? it uses some syntax i dont recognize like how you used cnt and outputted it and the lone t.


Customer_ID Customer Name                                                                                         Account_Type Customer_ID cnt
----------- ----------------------------------------------------------------------------------------------------- ------------ ----------- -----------
B0190102    Rudyard Margaret                                                                                      CA           B0190102    1
B0190706    Kipling Rudyard                                                                                       CD           B0190706    3
B0190706    Kipling Rudyard                                                                                       CA           B0190706    3
B0190706    Kipling Rudyard                                                                                       CD           B0190706    3
B0190706    Kipling Rudyard                                                                                       SA           B0190706    3
R0198100    Kipling Rudyard                                                                                       CA           R0198100    2
R0198100    Kipling Rudyard                                                                                       SA           R0198100    2

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-10 : 00:29:55

Check if this is what you are looking after ...

SELECT Customer.Customer_ID, Last_Name + ' ' + First_Name AS 'Customer Name', Account_Type,Count(Distinct Account_Type) NoOfAccounts
FROM Customer
INNER JOIN Account
ON Customer.Customer_ID = Account.Customer_ID
WHERE Last_Name = 'Rudyard' OR First_Name = 'Rudyard'
Group by Customer.Customer_ID, Last_Name + ' ' + First_Name,Account_Type
ORDER BY Customer.Customer_ID, Account_Type

Cheers
MIK

baseball43v3r`
Starting Member

8 Posts

Posted - 2011-03-10 : 00:38:58

close MIK! except the last column only has all 1's in it. its grouping the customer ids correctly, but only showing 1 for each account type, even if there are multiple account types associated with that customer id.


Customer_ID Customer Name                                                                                         Account_Type NoOfAccounts
----------- ----------------------------------------------------------------------------------------------------- ------------ ------------
B0190102    Rudyard Margaret                                                                                      CA           1
B0190706    Kipling Rudyard                                                                                       CA           1
B0190706    Kipling Rudyard                                                                                       CD           1
B0190706    Kipling Rudyard                                                                                       SA           1
R0198100    Kipling Rudyard                                                                                       CA           1
R0198100    Kipling Rudyard                                                                                       SA           1

(6 row(s) affected)

baseball43v3r`
Starting Member

8 Posts

Posted - 2011-03-10 : 00:43:10

i took out the word DISTINCT in the COUNT in the SELECT and that did the trick!

quick question. Does order matter when you GROUP, WHERE, or ORDER BY? and if so, why?

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-10 : 00:48:29

well thats what i perceived from your original post. The Last Column is showing 1, Because it has been grouped on the basis of three columns CustID,name and acct type. If you want it only on the basis of Customer then the query should be following

SELECT Customer.Customer_ID, Last_Name + ' ' + First_Name AS 'Customer Name',Count(Distinct Account_Type) NoOfAccounts
FROM Customer
INNER JOIN Account
ON Customer.Customer_ID = Account.Customer_ID
WHERE Last_Name = 'Rudyard' OR First_Name = 'Rudyard'
Group by Customer.Customer_ID, Last_Name + ' ' + First_Name
ORDER BY Customer.Customer_ID

Still if this is not correct then come up with the required output for the above listed (in original post) data..?

Cheers
MIK

baseball43v3r`
Starting Member

8 Posts

Posted - 2011-03-10 : 00:59:03

no Mik your solution worked fine, i just had to take out the distinct, here is the final output.


Customer_ID Customer Name                                                                                         Account_Type NoOfAccounts
----------- ----------------------------------------------------------------------------------------------------- ------------ ------------
B0190102    Rudyard Margaret                                                                                      CA           1
B0190706    Kipling Rudyard                                                                                       CA           1
B0190706    Kipling Rudyard                                                                                       CD           2
B0190706    Kipling Rudyard                                                                                       SA           1
R0198100    Kipling Rudyard                                                                                       CA           1
R0198100    Kipling Rudyard                                                                                       SA           1

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-03-10 : 01:07:03

thats good to hear

Cheers
MIK

baseball43v3r`
Starting Member

8 Posts

Posted - 2011-03-10 : 03:04:36

hey one final question on this.. how do i make the column width for the name shorter? i'm thinking its because the names are VARCHAR(50) or something like that. but is there a way to format this so its only the width of the name or whatever is in the column?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources