Solved

Sum and "sub" sum into the same query

Posted on 2004-10-05
11
930 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
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.

943 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

5 Experts available now in Live!

Get 1:1 Help Now