SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL statement multiplies SUM field result
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jafari1983
Starting Member

Azerbaijan
9 Posts

Posted - 06/19/2011 :  06:15:57  Show Profile  Reply with Quote
This is my table:



This is what I want to do:





And this is my SQL code

SELECT T1.BARCODE, T1.U_PRICE, SUM(T1.QTY) AS QTY,
SUM(T1.TOTAL1) AS TOTAL1, T2.Description
FROM TABLE1 T1 INNER JOIN TABLE1 T2
ON T1.BARCODE=T2.BARCODE

WHERE T1.OPERA1="SELL" AND T2.OPERA1="BUY"

GROUP BY T1.BARCODE, T1.U_PRICE, T2.Description


Thanks in advance,
Aqshin




La Fata illa Ali, la seyfa illa Zulfuqar

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 06/19/2011 :  08:24:10  Show Profile  Reply with Quote
There seems to be some inconsistencies in your description and/or data that makes it hard for me to understand the data and the query.

1. In the first table, rows 1 and 2 have a description, but 3 and 4 do not, even though the barcode is the same.

2. Is the second table the desired output, or is it another table with more transaction data? If it is the desired output, what is the structure of the table T2 that you have in your query?

3. You are joining on T2.Opera1 = 'BUY' and T1.Opera1 = 'SELL'. Does this mean you are recording a transaction between two parties, T1 being the data for one side of the transaction and T2 the other side?


In any case, see if this query gives you the results you are looking for:
select
   T1.BarCode,
   T1.U_PRICE,
   T2.Description,
   SUM(T1.QTY) AS QTY,
   SUM(T1.TOTAL1) AS TOTAL1
from
   Table1 T1
group by
   T1.BarCode,
   T1.U_PRICE,
   T2.Description
Go to Top of Page

jafari1983
Starting Member

Azerbaijan
9 Posts

Posted - 06/19/2011 :  08:52:44  Show Profile  Reply with Quote
The second table is desired output. I have only one table

I keep sell and buy operations in one table. I don't know if it's right.

First two lines are buying operations. When I buy I insert medicine names and other informations (Information is too much. That's why I don't want to repeat the same thing everytime when I sell), but when I sell I only insert BarCode.

La Fata illa Ali, la seyfa illa Zulfuqar
Go to Top of Page

jafari1983
Starting Member

Azerbaijan
9 Posts

Posted - 06/19/2011 :  09:08:03  Show Profile  Reply with Quote
I want to get this result:
IF barcodes are the same. SUM quantity and Total amount where the OPERA=sell and select medicine description from where the OPERA=buy



Edited by - jafari1983 on 06/19/2011 09:08:52
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 06/19/2011 :  10:33:31  Show Profile  Reply with Quote

select
   T1.BarCode,
   T1.U_PRICE,
   MAX(T1.Description) as Description,
   SUM(T1.QTY) AS QTY,
   SUM(T1.TOTAL1) AS TOTAL1
from
   Table1 T1
where
   T1.Opera1 = 'SELL'
group by
   T1.BarCode,
   T1.U_PRICE
While this would work, if you are able to change the design, a better design would be to keep the information about the medications in one table and the information about the transactions in another table.
Go to Top of Page

jafari1983
Starting Member

Azerbaijan
9 Posts

Posted - 06/19/2011 :  11:04:09  Show Profile  Reply with Quote
It doesn't give the Description in output. Anyway, maybe You help me to design my tables ?

Everyday I do these operations:
1. Buy medicines
2. Sell them
3. Return of medicines from the buyers to me
4. Return of medicines to suppliers that i buy medicines from
5. Edit all 4 operations in case of mistake.

What do You suggest me?
How many tables should I create, what should I save in them.

Edited by - jafari1983 on 06/19/2011 11:05:58
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 06/19/2011 :  14:08:44  Show Profile  Reply with Quote
Here is one way to do it. Obviously I know much less about your business needs than you do, so if this does not work for you, or if it does not make sense, post the details.

One thing I am not too crazy about in this is that the inventory balance must be kept updated when you sell,buy etc. But that can be reliably done using a trigger.
-- TABLES
create table dbo.Drugs
(
  drug_id int not null primary key,
  drug_name varchar(255) not null,
  drug_barcode varchar(31) not null,
  inventory_balance int not null check (inventory_balance >= 0)
);

CREATE UNIQUE INDEX index_name
ON dbo.Drugs (drug_barcode);

create table dbo.Drug_Transactions
(
   trans_id int not null identity(1,1) primary key,
   drug_barcode varchar(31) not null references drugs(drug_barcode),
   trans_type varchar(15) not null check(trans_type in ('CUST_SALE','CUST_RETURN','SUPPLIER_BUY','SUPPLIER_RETURN')),
   trans_quantity int not null,
   trans_price decimal(9,2) not null,
   trans_date datetime not null default getdate()
)

-- SAMPLE DATA
insert into drugs values (1,'Aspirin',1111,0);

insert into drug_Transactions values (1111,'SUPPLIER_BUY',10,1.00,getdate());
insert into drug_Transactions values (1111,'SUPPLIER_BUY',15,1.00,getdate());
insert into drug_Transactions values (1111,'CUST_SALE',2,1.00,getdate());
insert into drug_Transactions values (1111,'CUST_SALE',3,1.00,getdate());

-- DOWNSIDE OF THE DESIGN IS THAT YOU HAVE TO TAKE CARE TO KEEP THE INVENTORY_BALANCE UPDATED.
update drugs set inventory_balance = 20 where drug_barcode = 1111;

-- QUERY TO FIND CUSTOMER SALES.
select
	m.drug_name,
	m.drug_barcode,
	t.trans_type,
	sum(t.trans_quantity) as Quantity
from
	drugs m
	inner join drug_Transactions t
		on t.drug_barcode = m.drug_barcode
where
	t.trans_type = 'CUST_SALE'
group by
	m.drug_name,
	m.drug_barcode,
	t.trans_type;
Go to Top of Page

jafari1983
Starting Member

Azerbaijan
9 Posts

Posted - 06/19/2011 :  14:33:45  Show Profile  Reply with Quote
Thank You very much. You gave me an idea about the designing my tables. I am gonna redesign them again.

Thanks a lot.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000