Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sum and "sub" sum into the same query

Posted on 2004-10-05
11
Medium Priority
?
952 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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

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!

Question has a verified solution.

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

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

610 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