[Closed] sql help

select count(*), ac.accountId from
filteredaccount ac, filteredcontact fc
where fc.accountId = ac.accountId
group by ac.accountId

Assuming that accountId is the column linking the two tables and the column exists on both tables i.e. foreign key or similar.

That should show a count of contacts for each account, any good ?

ok so if i get this right i would use a subquery joined in so:

SELECT
count(filteredaccount.accountid) as accounts
,contactsCount
FROM
filteredaccount b
LEFT JOIN
(SELECT COUNT(filteredcontact,contactid) as contactsCount,AccountId FROM filteredcontact
GROUP BY AccountId ) a
ON
a.AccountId = B.AccountId

GROUP BY contactsCount

Allthepies I think yours just gives contacts by accountId i think he wants more than that

mrmo, how you finding SSRS, you got 2008?

Yes, I think you're right 🙂

titus, yes i do want more, i have got to the count of contacts by account, i can then use the reporting side of SSRS to do the grouping and get what i want, which is how many accounts have x contacts.

What i am trying to do is achieve this solely in SQL.

Titus could you explain a bit more? contactscount, a.accountid b.accountid, etc, i am still new to SQL and have spent most of this morning so far trying to get a select case to work.

yes SSRS 2008, to be honest i am a little perplexed, there are a few really simple formatting issues that it seems unable to do, i want to be able to rotate text through 90 degrees to save some space and haven't found the option yet? but the ability to use code is good, just got to get upto speed on VB. background is Excel and Business Objects.

Seems to have issues passing parameters between reports and and subreports, but i am wondering if that is actually our crm install at fault rather than SSRS. Only been using it a few months so still on a learning curve.

The nature of the tool means i need to learn SQL which may help but I think i still prefer Business Objects.

Ok so from that, i think what I have written for you is doing what you want to achieve. To go through it a bit more:

You want to start with the inner select ( i have altered this from above as you are going to need the join inside this) :

SELECT
COUNT(filteredcontact,contactid) as contactsCount,
AccountId
FROM FilteredAccount INNER JOIN
FilteredContact ON FilteredAccount.accountid = FilteredContact.accountid
GROUP BY AccountId

That gives you the count of contacts by account id

You then need to wrap that up and count how many accounts have each number of contacts

SELECT
count(accountid) as accounts
,contactsCount
FROM
-- Our inner select
(SELECT
COUNT(filteredcontact,contactid) as contactsCount,
AccountId
FROM INNER JOIN
FilteredContact ON FilteredAccount.accountid = FilteredContact.accountid
GROUP BY AccountId)AS InnerSelect

GROUP BY contactsCount

that is a bit cleaner and clearer hopefully. I have made some 'improvements' from above so dont try and compare them too much
the a. b. stuff was just me giving the tables an alias to save writing out the name all the time (ie lazyness!)

And contactsCount is the name im giving the column that holds the contacts count per account

SSRS text alingment you are probably after "WritingMode" in the properties window once you have seleted the textbox

[url= http://www.techonthenet.com/sql/having.php ]This page[/url] might be of assistance then - assuming you are looking for accounts with greater than or less than a given number of contacts? (or even a specific number of contacts?

SSRS text alingment you are probably after "WritingMode" in the properties window once you have seleted the textbox

Thanks, just what i wanted.

I'll have a look at the other bits later.

just what i wanted, thanks.

Yay, i helped 🙂