Rollup/Cube and Count

Rollup/Cube and Count

Post by Tore Fremmersv » Sun, 04 Nov 2001 06:29:31



Hi all!

I've tried everything, but cannot get my query right. Believe I should
use rollup or cube to solve this, but don't know how. Hope you can
help.

This is the issue:
2 tables:

Organizations:
Org_Id
Org_Name
Org_Phone
Org_Fax

Contacts:
Con_Id
Org_Id
Con_Name
Con_Phone
Con_Cellular

My query should return:

Org_Id | Org_Name | Org_Phone | Con_Id | Con_Name | Con_Phone | Number
of Cons for Org

Actually I almost got it right with this one:

SELECT dbo.Organizations.Org_Name, dbo.Contacts.Con_Name,
count(dbo.Contacts.Org_Id) as Total
FROM dbo.Contacts
RIGHT OUTER JOIN dbo.Organizations ON dbo.Contacts.Con_Id =
dbo.Organizations.Org_Id
GROUP BY dbo.Organizations.Org_Name, dbo.Contacts.Con_Name WITH ROLLUP
ORDER BY dbo.Organizations.Org_Name

but when I try to add more fields to the query everything goes
wrong...

Thank you for you help!

Best regards
Tore Fremmersvik

 
 
 

Rollup/Cube and Count

Post by Ivan Demkovitc » Sun, 04 Nov 2001 07:30:17


First of all you have
Quote:> RIGHT OUTER JOIN dbo.Organizations ON dbo.Contacts.Con_Id =
> dbo.Organizations.Org_Id

(Con_Id = Org_Id ?????)

Then try this (didn't test)

SELECT dbo.Organizations.Org_Name, dbo.Contacts.Con_Name,
 ( SELECT count(dbo.Contacts.Org_Id) FROM dbo.Contacts WHERE
dbo.Contacts.OrgID =
    = dbo.Organizations.OrgID Group By dbo.Contacts.Org_Id) as Total
 FROM dbo.Contacts, dbo.Organizatons
WHERE dbo.Contacts.Org_Id =
 dbo.Organizations.Org_Id


Quote:> Hi all!

> I've tried everything, but cannot get my query right. Believe I should
> use rollup or cube to solve this, but don't know how. Hope you can
> help.

> This is the issue:
> 2 tables:

> Organizations:
> Org_Id
> Org_Name
> Org_Phone
> Org_Fax

> Contacts:
> Con_Id
> Org_Id
> Con_Name
> Con_Phone
> Con_Cellular

> My query should return:

> Org_Id | Org_Name | Org_Phone | Con_Id | Con_Name | Con_Phone | Number
> of Cons for Org

> Actually I almost got it right with this one:

> SELECT dbo.Organizations.Org_Name, dbo.Contacts.Con_Name,
> count(dbo.Contacts.Org_Id) as Total
> FROM dbo.Contacts
> RIGHT OUTER JOIN dbo.Organizations ON dbo.Contacts.Con_Id =
> dbo.Organizations.Org_Id
> GROUP BY dbo.Organizations.Org_Name, dbo.Contacts.Con_Name WITH ROLLUP
> ORDER BY dbo.Organizations.Org_Name

> but when I try to add more fields to the query everything goes
> wrong...

> Thank you for you help!

> Best regards
> Tore Fremmersvik


 
 
 

Rollup/Cube and Count

Post by Tore Fremmersvi » Mon, 05 Nov 2001 18:51:30


Thanks Ivan, your reply really helped.

Only one thing though - what about orgs not having a contact? They are not included in the result. Any ideas how to solve this?

Thanks!

Best regards
Tore Fremmersvik

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

1. How do you rollup count data when making a data cube

I do not know how to build a cube that keeps valid counts at different level
in a dimension.  For example, lets say you have grocery store transaction
data, thats organized by store and then by department. You want to count how
many unique customers shoped during a given month by department and then by
store.

Lets say an individual in given month buys items from the following
departments in a grocery store: meat, dairy, and cosmetics. For a given
month this customer would increment the count of customers in each
department by 1. However, if you aggregate up to the store level you would
have a count of 3 when you only want to increment the store count by 1.

Does anyone know how to solve this problem. I'm using both Cognos and
Microsoft Pivot Table services for office 2000.

Aaron Moynahan

2. Start SQLAgent Service from Code

3. Rollup of a Cube with lower level calcs

4. Formatting data in a percent field

5. Custom rollup formulas and virtual cubes

6. Substitution Variable limitation of 3000 characters?

7. Failed to update custom rollup for cube levels

8. ORA-01545.... Thanks All

9. custom rollup but in 1 cube only ?

10. Disctinct count vs custom rollup

11. ROLLUP OR CUBE?

12. Local Cube using DDL with custom rollup.

13. Error:Failed to update custom rollup for cube levels