Need SQL Query Urgent - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Need SQL Query Urgent

Author

Topic

sankararam
Starting Member

1 Post

Posted - 2010-08-11 : 21:36:49

Hello Friends -

I need to write a query using GROUP BY and I need your help

My table is as follows: I have three tables A,B and C as follows

Table A

Vendor id Vendor Name

A1 AAA
B1 BEST BUY
C1 CIRCUIT CITY

Table B

Vendor ID and Address (address1, address2, city,contry, zip,telephone)

ID ADDRESS CITY CONTRY ZIP

A1 ABC STRETT, NEW YORK USA 00101
B1 EAST STREET,NEW YORK USA 01730
C1 CIRCUIT CITY ATLANTA USA 33923

Table C

Vendor iD, basic amout, disc amt, tax amt, check number

VENDOR BAS AMT DISC TAX CHEQUE NO

A1 100 5 5 400023
B1 250 50 50 209438
A1 200 10 20 440480
B1 200 0 10 308089
B1 450 100 50 0808800
C 1000 300 150 0000898

I NEED TO DISPLAY MY RESULT AS FOLLOWS:
Vendor id, vendor name, address, total checks against the vendor and total amount (group) so that one single amount for the vendor for all the transactions

RESULT

A1 AAA ADDRESS 2 (COUNT FOR CHECIK NO) 310
BA BEST BUY 3 850
C1 CIRCUIT CIT 1 750

(SUM THE AMOUNT AGAINST EACH VENDOR AND NO OF RECORDS WITH ADDRESS)

I need a query to get the above result. Please help me

My advance thanks

sneupane
Starting Member

28 Posts

Posted - 2010-08-11 : 22:14:34

I tried to do what you asked but might not be the exact solution coz' the question is not that clear.

Select
VendorID
,VendorName
,Address ,
,count(Check Number) as TotalChecks
,sum(basic amount) as TotalAmount

from Table TA
-----------------Join Table TB------------------
left join(select VendorID,Address from TB )TB
on TA.VendorID = TB.VendorID

-----------------Join Table TC------------------

left join(select VendorID,Basic amount,Check Number, from TC )TC
on TA.VendorID = TC.VendorID

Group By

VendorID
,VendorName
,Address

Sar

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-12 : 04:38:00

How you are calculating amout is it basic-disc+tax ?
If yes then might be this one -


SELECT C.VendorID, VendorName, Address, COUNT(CheckNumber), SUM(basic-disc+tax) 
FROM C INNER JOIN B ON C.VendorID = B.VendorID 
INNER JOIN A ON B.VendorID = A.VendorID 
GROUP BY C.VendorID, VendorName, Address

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-12 : 13:23:31

but sample output certainly doesnt fit that formula as i cant understand how OP got 750 for C

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-08-13 : 02:48:10

Yes Visakh,

Thats why I was asking to him that how you are calculating
but for first row 310 is coming with this formula.

so i thought there might me typo error.

quote:
Originally posted by visakh16

but sample output certainly doesnt fit that formula as i cant understand how OP got 750 for C

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources