Singletrack Mountain Bike Magazine

• Overview
• Bike Forum
• Chat Forum
• For Sale
• Wanted
• Forum Help
• Classified Rules

Chat Forum

1. mrmo - Member

   i am currently using SSRS and am having a few issues getting my head around something i am trying to achieve,

   I have two joined tables, one a list of accounts and one a list of contacts. I am trying to show a count of accounts grouped by the number of contacts within that account.

   I think what i am trying to do is this

   select count(filteredaccount.accountid) as accounts, count(filteredcontact,contactid) as contacts
   From FilteredAccount INNER JOIN
   FilteredContact ON FilteredAccount.accountid = FilteredContact.accountid
   group by count(filteredcontact.contactid)

   but i can't do the group by, can anyone offer a solution. I have bodged it using the report tool to do the grouping but would like to know if it can be done in SQL only.

   Posted 4 months ago #
2. allthepies - Member

   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 ?

   Posted 4 months ago #
3. titusrider - Member

   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?

   Posted 4 months ago #
4. allthepies - Member

   Yes, I think you're right

   Posted 4 months ago #
5. mrmo - Member

   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.

   Posted 4 months ago #
6. mrmo - Member

   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.

   Posted 4 months ago #
7. titusrider - Member

   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

   Posted 4 months ago #
8. titusrider - Member

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

   Posted 4 months ago #
9. toby1 - Member

   This page 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?

   Posted 4 months ago #
10. mrmo - Member

    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.

    Posted 4 months ago #
11. mrmo - Member

    just what i wanted, thanks.

    Posted 4 months ago #
12. titusrider - Member

    Yay, i helped

    Posted 4 months ago #

RSS feed for this topic

Reply

You must log in to post.