How to write a query for totals (difficult???)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How to write a query for totals (difficult???)

Author

Topic

Incognito
Starting Member

49 Posts

Posted - 2002-07-18 : 05:12:17

Hi,

I have to 2 queries which I use for some statistics.

Query 1:

Select Businessunits.Omschrijving, Count(OfferteID) as VerzondenOffertes
From Businessunits, Medewerkers, Offertes
Where Businessunits.BusinessunitID = Medewerkers.BusinessunitID and Medewerkers.MedewerkerID = Offertes.MedewerkerID
and (Aanmaakdatum Between @Datum1 and @Datum2) and SysteemStatus = 'actief' and Eindgebruiker <> 3503167 and Eindgebruiker <> 15308
and OfferteStatusID = 1
Group By Businessunits.Omschrijving

Query 2: For Totals

Select Businessunits.Omschrijving, Count(OfferteID) as TotaleOffertes
From Businessunits, Medewerkers, Offertes
Where Businessunits.BusinessunitID = Medewerkers.BusinessunitID and Medewerkers.MedewerkerID = Offertes.MedewerkerID
and (Aanmaakdatum Between @Datum1 and @Datum2) and SysteemStatus = 'actief' and Eindgebruiker <> 3503167 and Eindgebruiker <> 15308
Group By Businessunits.Omschrijving

Result 1:

Gebouwautomatisering 9
Industrial Electronics 27
Industriële Automatisering 8

Result 2:

Focussed ICT-Solutions 10
Gebouwautomatisering 22
Industrial Electronics 35
Industriële Automatisering 13

The only difference between the 2 queries is the OffertestatusID which I use in query 1 for filtering.

Now I want no 2 results but 1 like:

Focussed ICT-Solutions 0 of 9
Gebouwautomatisering 9 of 22
Industrial Electronics 27 of 35
Industriële Automatisering 8 of 13

Is this possible?

Thnx for all the help.

GIA

Edited by - Incognito on 07/18/2002 06:04:48

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-18 : 06:09:25

would something along the lines of the following work....???

select field1, sum(filteron), sum(filteroff) from
(select field1, filteron = case when filterfield = "" then 0 end, filteroff = case when filterfield <> "" then 1 end from table1) as a
group by field1

technically the syntax is right....but not sure about the answer...my test data isn't the best...but i think it's on the right track

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2002-07-18 : 06:14:16

have just noticed that ....

select field1, sum(filteron), sum(filteroff) from
(select field1, filteron = 1, filteroff = case when filterfield <> "" then 1 end from table1) as a
group by field1

should give the proper result....filteron =1 will count every record, fileteroff will count the 'filtered records'....you will need to adjust the condition to suit your own needs....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources