SubQuery - how to Sum & Count 2

[Currana]

Hi everyone,


I’m not sure if this is a SubQuery or not, but may I ask for some help please ?

I have 2 queries, the first is :-

SELECT   Clients.Clientname,
         SUM(Items.Itemexvat)      AS [ExTax],
         SUM(Items.Itemvatamount)  AS [Tax],
         SUM(Items.Itemincvat)     AS [IncTax]
FROM (((Clients INNER JOIN Jobs ON Clients.Clientid = Jobs.Jobtoclients)
                INNER JOIN Suppliers ON Jobs.Jobspri = Suppliers.Suppliertojobs)
                INNER JOIN Invoices ON Suppliers.Supplierpri = Invoices.Invoicetosuppliers)
                INNER JOIN Items ON Invoices.Invoicepri = Items.Itemtoinvioice
GROUP BY Clients.Clientname;

Which gives me a result of :-

ClientName		ExTax		Tax		IncTax
Client A		1000		100		1100
Client B		1500		150		1650
Client C		2000		200		2200

My second query is :-

SELECT   Clients.Clientname,
         COUNT(Jobs.Nickname)  AS [JobCount]
FROM     Clients
         INNER JOIN Jobs ON Clients.Clientid = Jobs.Jobtoclients
GROUP BY Clients.Clientname;

That query list my data like :-

ClientName		JobCount
Client A			2
Client B			1
Client C			2

Could someone show me how I could have a DataSet looking this this, please :-

ClientName		JobCount	ExTax		Tax		IncTax
Client A			2		1000		100		1100
Client B			1		1500		150		1650
Client C			2		2000		200		2200

I’ve tried putting together a SubQuery in my first query, but, to be honest, I really don’t understand how to build a SubQuery and I’m getting nowhere.

Many thanks,
Amy.

 

[JerryKlmns]

Have you tried to include this field in your first query?

COUNT(*) AS [JobCount]

 

[Currana]

Yes,

If I run this query :-

SELECT   Clients.Clientname, Count(*) AS [JobCount],
         SUM(Items.Itemexvat)      AS [ExVAT],
         SUM(Items.Itemvatamount)  AS [VAT],
         SUM(Items.Itemincvat)     AS [IncVAT]
FROM     (((Clients INNER JOIN Jobs ON Clients.Clientid = Jobs.Jobtoclients)
                    INNER JOIN Suppliers ON Jobs.Jobspri = Suppliers.Suppliertojobs)
                    INNER JOIN Invoices ON Suppliers.Supplierpri = Invoices.Invoicetosuppliers)
                    INNER JOIN Items ON Invoices.Invoicepri = Items.Itemtoinvioice
GROUP BY Clients.Clientname;

I get results like :-

ClientName		JobCount	ExTax		Tax		IncTax
Client A			362		1000		100		1100
Client B			291		1500		150		1650
Client C			142		2000		200		2200

The JobCount field seems to count the total number of items in the table that relates to each ClientName.

Thanks for posting though,

Amy

 

[r937]

save your two queries and then write a simple join on them



r937.com | rudy.ca

 

[Currana]

Hi r937,

save your two queries and then write a simple join on them

Unfortunately, I can't do that. My Application is written in Delphi (Pascal), it just uses MS Access tables to read & write the data (Via ADO components). Queries etc can be written in MS Access (to generate the SQL text), but need to be put thru an ADOQuery component.

Thanks for the post.

Amy.

 

[lespaul]

I use delphi, but not the ADO components, try this query (typed not tested):

SELECT   Clients.Clientname,
         SUM(Items.Itemexvat)      AS [ExTax],
         SUM(Items.Itemvatamount)  AS [Tax],
         SUM(Items.Itemincvat)     AS [IncTax],
         JOBCOUNT
FROM ((((Clients INNER JOIN Jobs ON Clients.Clientid = Jobs.Jobtoclients)
                INNER JOIN Suppliers ON Jobs.Jobspri = Suppliers.Suppliertojobs)
                INNER JOIN Invoices ON Suppliers.Supplierpri = Invoices.Invoicetosuppliers)
                INNER JOIN Items ON Invoices.Invoicepri = Items.Itemtoinvioice)
INNER JOIN (SELECT   Clients.Clientname,
         COUNT(Jobs.Nickname)  AS [JobCount]
FROM     Clients
         INNER JOIN Jobs ON Clients.Clientid = Jobs.Jobtoclients
GROUP BY Clients.Clientname) N ON N.ClientName = Clients.ClientName
GROUP BY Clients.Clientname, JOBCOUNT;

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[Currana]

Leslie,

Just ran it through my app and got exactly what I needed, thank you.

I need to spend some time reading thru what you have done to try and figure it all out.

However, I'm really grateful to you and the other posters for helping out.

Amy.

 

[lespaul]

I basically included your second query as part of a JOIN:

SELECT   Clients.Clientname,
         SUM(Items.Itemexvat)      AS [ExTax],
         SUM(Items.Itemvatamount)  AS [Tax],
         SUM(Items.Itemincvat)     AS [IncTax],
         [b]JOBCOUNT[/b]
FROM ((((Clients INNER JOIN Jobs ON Clients.Clientid = Jobs.Jobtoclients)
                INNER JOIN Suppliers ON Jobs.Jobspri = Suppliers.Suppliertojobs)
                INNER JOIN Invoices ON Suppliers.Supplierpri = Invoices.Invoicetosuppliers)
                INNER JOIN Items ON Invoices.Invoicepri = Items.Itemtoinvioice)
[b]INNER JOIN (SELECT   Clients.Clientname,
         COUNT(Jobs.Nickname)  AS [JobCount]
FROM     Clients
         INNER JOIN Jobs ON Clients.Clientid = Jobs.Jobtoclients
GROUP BY Clients.Clientname)[/b] N ON N.ClientName = Clients.ClientName
GROUP BY Clients.Clientname, [b]JOBCOUNT[/b];

and gave the results of that query the alias N. Then I did a normal join between the client name in your main table CLIENTS to the clientName field in the N result set. I added the field from the bolded query into your original select and to the original GROUP BY....

Glad to help!

leslie