Solved

Sum and "sub" sum into the same query

Posted on 2004-10-05
11
936 Views
Last Modified: 2008-02-01
Hi,

Let imagine those tables :

Activity   (Activity)
  Activity_ID
  Activity_Name
  ...

Time   (The time spend on a activity)
  Time_ID
  Time
  Activity_ID
  ...

Data_Type_ID     (Type of data related to a activity)
  Type_ID
  Type
  ...

Data     (The data for all activity)
  Data_ID
  Type_ID
  Data
  ...

Join_Table     (must exist because one activity can have multiple type of data related)
  Activity_ID
  Type_ID
  ...

I want to be able into the same query to get the SUM of Time.Time, the SUM of Data (soo far no trouble) AND the SUM of Time.Time, SUM of Data GROUP BY Activity_ID.

Is't possible or must I do two separate query?

I got something like that for the first part :

SELECT SUM(Time.Time) AS STime, SUM(Data.Data) AS SData
FROM Time
INNER JOIN Activity ON Activity.Activity_ID = Time.Activity_ID
INNER JOIN Join_Table ON Joint_Table.Activity_ID = Activity.Activity_ID
INNER JOIN Data_Type_ID ON Data_Type_ID.Type_ID = Join_Table.Type_ID
INNER JOIN Data ON Data.Type_ID = Data_Type_ID.Type_ID;


and this for the second part :

SELECT SUM(Time.Time) AS STime, SUM(Data.Data) AS SData, Activity.Activity
FROM Time
INNER JOIN Activity ON Activity.Activity_ID = Time.Activity_ID
INNER JOIN Join_Table ON Joint_Table.Activity_ID = Activity.Activity_ID
INNER JOIN Data_Type_ID ON Data_Type_ID.Type_ID = Join_Table.Type_ID
INNER JOIN Data ON Data.Type_ID = Data_Type_ID.Type_ID
GROUP BY Activity.Activity;

Is't possible to combine those two query into one query?
0
Comment
Question by:brodseba
  • 6
  • 5
11 Comments
 
LVL 26

Accepted Solution

by:
Hilaire earned 125 total points
ID: 12227577
Please give it a try

SELECT SUM(Time.Time) AS STime, SUM(Data.Data) AS SData,
Case when grouping(Activity.Activity) = 1 then 'Total' else Activity.Activity end as Activity
FROM Time
INNER JOIN Activity ON Activity.Activity_ID = Time.Activity_ID
INNER JOIN Join_Table ON Joint_Table.Activity_ID = Activity.Activity_ID
INNER JOIN Data_Type_ID ON Data_Type_ID.Type_ID = Join_Table.Type_ID
INNER JOIN Data ON Data.Type_ID = Data_Type_ID.Type_ID
GROUP BY Activity.Activity WITH ROLLUP
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12227905
The 'WITH ROLLUP' will add a grand total to the normal resultset.
The "case when grouping" this is just to give the total a relevant name instead of the default NULL value.

HTH

Hilaire
0
 

Author Comment

by:brodseba
ID: 12229188
I need to be able to fetch that row using PHP and odbc function.  Is't possible to assign a name to the grand total result using AS maybe?
0
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

Author Comment

by:brodseba
ID: 12229211
...or maybe the CASE section do just that?
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12229247
The case section does that
0
 

Author Comment

by:brodseba
ID: 12229328
Thank you!
0
 

Author Comment

by:brodseba
ID: 12237733
Now the problem is the following :

I have more than one flelds into the GROUP BY, so WITH ROLLUP give me weird result.

I.e. I added Name and Surname for employees, but now I got two total for each employee.

Activity      Name        Surname     STemps     SData
------------------------------------------------------------
Activity1    Luke          Solo            100          10
Total        Luke          Solo             100          10
Activity1    Luke          Skywalker    100          10
Activity2    Luke          Skywalker    200          10
Activity3    Luke          Skywalker    200          10
Total        Luke          Skywalker     500          30
Total        Luke                             600          40
Activity2   Janet         Clinton         10             60
Total       Janet          Clinton         10            60
Total       Janet                             10            60
...

SELECT SUM(Time.Time) AS STime, SUM(Data.Data) AS SData,
Case when grouping(Activity.Activity) = 1 then 'Total' else Activity.Activity end as Activity,
Employees.Name, Employees.Surname
FROM Time
INNER JOIN Employees ON Employees.Employee.ID = Time.Employee_ID
INNER JOIN Activity ON Activity.Activity_ID = Time.Activity_ID
INNER JOIN Join_Table ON Joint_Table.Activity_ID = Activity.Activity_ID
INNER JOIN Data_Type_ID ON Data_Type_ID.Type_ID = Join_Table.Type_ID
INNER JOIN Data ON Data.Type_ID = Data_Type_ID.Type_ID
GROUP BY Employees.Name, Employees.Surname, Activity.Activity WITH ROLLUP

I figure out that the ROLLUP give me a total for Name.  Is't possible to get a partial ROLLUP like with Oracle?  All I need is the total for each employee and a grand total.

We use Microsoft SQL Server 2000
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12238071
You can add conditions in the where clause
eg try

SELECT SUM(Time.Time) AS STime, SUM(Data.Data) AS SData,
Case when grouping(Activity.Activity) = 1 then 'Total' else Activity.Activity end as Activity,
Employees.Name, Employees.Surname
FROM Time
INNER JOIN Employees ON Employees.Employee.ID = Time.Employee_ID
INNER JOIN Activity ON Activity.Activity_ID = Time.Activity_ID
INNER JOIN Join_Table ON Joint_Table.Activity_ID = Activity.Activity_ID
INNER JOIN Data_Type_ID ON Data_Type_ID.Type_ID = Join_Table.Type_ID
INNER JOIN Data ON Data.Type_ID = Data_Type_ID.Type_ID
where not ((grouping(Employees.Name) = 1 or grouping(Employees.Surname) = 1) and grouping(Acitvity.activity)=1)
GROUP BY Employees.Name, Employees.Surname, Activity.Activity WITH ROLLUP
0
 

Author Comment

by:brodseba
ID: 12238322
I got the following error : An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

Here the query (it's in french, but I think being in France you will be able to read it) :

SELECT
CASE WHEN GROUPING(Activite) = 1 THEN
  'Total' ELSE Activites.Activite
END AS Activite,

CASE WHEN GROUPING(Prenom) = 1 THEN
  '' ELSE Ressources.Prenom
END AS Prenom,

CASE WHEN GROUPING(Nom) = 1 THEN
  '' ELSE Ressources.Nom
END AS Nom,

SUM(Temps.Temps) AS STemp

FROM Temps
INNER JOIN Activites ON Activites.Activite_ID = Temps.Activite_ID
INNER JOIN Ressources ON Ressources.Ressource_ID = Temps.Ressource_ID

WHERE NOT((GROUPING(Prenom) = 1 OR GROUPING(Nom) = 1) AND GROUPING(Activite) = 1)

GROUP BY Prenom, Nom, Activite WITH ROLLUP
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12238483
Another trial ...

SELECT
CASE WHEN GROUPING(Activite) = 1 THEN
  'Total' ELSE Activites.Activite
END AS Activite,
CASE WHEN GROUPING(Prenom) = 1 THEN
  '' ELSE Ressources.Prenom
END AS Prenom,
CASE WHEN GROUPING(Nom) = 1 THEN
  '' ELSE Ressources.Nom
END AS Nom,
SUM(Temps.Temps) AS STemp
FROM Temps
INNER JOIN Activites ON Activites.Activite_ID = Temps.Activite_ID
INNER JOIN Ressources ON Ressources.Ressource_ID = Temps.Ressource_ID
GROUP BY Prenom, Nom, Activite WITH ROLLUP
HAVING NOT((GROUPING(Prenom) = 1 OR GROUPING(Nom) = 1) AND GROUPING(Activite) = 0)
0
 

Author Comment

by:brodseba
ID: 12238814
Work like a charm but the grand total doesn't appear anymore.  I'm difficult :-)

Un gros merci pour tout déjà.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question