?
Solved

Sum and "sub" sum into the same query

Posted on 2004-10-05
11
Medium Priority
?
949 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 26

Accepted Solution

by:
Hilaire earned 500 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 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