Solved

Sum and "sub" sum into the same query

Posted on 2004-10-05
11
929 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
 

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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now