multiple aggregate functions with multiple tables in SQL Server

Question

I am trying to get data for entered person, I want to pull out data as No of invoices and No of line items for particular person.

The output is

Entered_by  No of line items
CD               9
CD               136084
deepa             7
deepa             18
dolly             757
dolly             22350
kroshni         666
kroshni         16161
lokesh           4
lokesh           999
MHeera           639
MHeera             20427
nandini            7
nandini            5318

Here the data in No of line items is mixing of both ’ No of line items’ count and ‘No of invoices’ count, I want to show like

Entered_by  No of line items    No of invoices
CD               136084              9
deepa               18                7
dolly               22350               757

Please help me with this somebody ….. 

Here is the T-SQL query

select ENTERED_BY, count(entered_by) 'NO OF LINE ITEMS'
from im_invoice, im_invoice_line_item, im_invoice_inventory 
where invoice_rid = invoice_fk
and invoice_inventory_rid = invoice_inv_fk
and enter_date between dateadd(mm, -3, getdate()) and dateadd(mm,0,getdate())
group by entered_by

union 

select entered_by, count(invoice_num) 'NO OF INVOICES' from im_invoice
where enter_date between dateadd(mm, -3, getdate()) and dateadd(mm,0,getdate())
group by entered_by

Can you clarify the relationships between the three tables? Which tables do the columns invoice_rid, invoice_fk, invoice_inventory_rid and invoice_inv_fk belong to? — Joe Stefanelli
– Joe Stefanelli, Commented Apr 12, 2011 at 14:43

Mack · Accepted Answer · 2011-04-13 08:12:31Z

1

As Joe said, if you give us a more detailed description we can give you better answers, but until then, quick and dirty way to accomplish this is as follows:

Get rid of the union
Turn the 2 queries into derived tables
Select from them joining on entered_by.

Eg.

SELECT LineItems.ENTERED_BY, [NO OF LINE ITEMS], [NO OF INVOICES] 
FROM
(SELECT ENTERED_BY,COUNT(entered_by) 'NO OF LINE ITEMS' 
FROM im_invoice, im_invoice_line_item,im_invoice_inventory   
WHERE invoice_rid = invoice_fk  
AND invoice_inventory_rid = invoice_inv_fk
AND enter_date BETWEEN dateadd(mm, -3, getdate()) AND dateadd(mm,0,getdate())
GROUP BY entered_by) AS LineItems 
INNER JOIN 
(SELECT entered_by, count(invoice_num) 'NO OF INVOICES' 
FROM im_invoice  
WHERE enter_date BETWEEN dateadd(mm, -3, getdate()) AND dateadd(mm,0,getdate())  
GROUP BY entered_by ) AS invoices 
ON invoices.entered_by = LineItems.ENTERED_BY

edited Apr 13, 2011 at 8:12

answered Apr 12, 2011 at 15:34

Mack

2,5521 gold badge26 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

multiple aggregate functions with multiple tables in SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related