need help in queries using sql

  • Thread starter Thread starter KG
  • Start date Start date
K

KG

I am doing a query operation. Over here, i have 4 fileds. One of the
is Name,transactionType,weight and some calculation filed. I need t
query all the names transaction type whose weight is less than 100
this is done.

Problem is I need to output all others whose weight is less than 100
thats ok. but i have to sort according to the transaction type an
replace the name as "others" in the quer

this is my query

SELECT username='Others' AS Others, dbo_trans.transtype A
[Transaction Type], Count(dbo_trans.trans) AS [Total Transactions]
Sum(Abs([dbo_trans].[weight]))/44000 AS TL
FROM dbo_tran
WHERE (((dbo_trans.datetime)>=[Start Date (mm/dd/yyyy)] An
(dbo_trans.datetime)<=DateAdd("d",1,[End Date (mm/dd/yyyy)]))
GROUP BY dbo_trans.transtype, dbo_trans.usernam
HAVING ((Count(dbo_trans.trans))<50)

this changes the column name but not the column contents

K
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure about your specification. Your query "says": display a summary
of the count of transactions and calculated weight (TLE) for records in
a user-defined date range whose total transactions were less than 50.
BTW, using the word "datetime" for a column name is wrong, since that
word is an SQL keyword. You should use a more descriptive name like
TransDate, or whatever suits your purposes.

If you just want to sort by the transaction type and set up a column
that has "Others" as its value:

SELECT 'Others' AS Others, transtype AS [Transaction Type],
Count(trans) AS [Total Transactions],
Sum(Abs([weight]))/44000 AS TLE
FROM dbo_trans
WHERE datetime >= [Start Date (mm/dd/yyyy)] And
datetime <= DateAdd("d",1,[End Date (mm/dd/yyyy)]
AND [weight] < 100
GROUP BY transtype
HAVING Count(trans) < 50
ORDER BY transtype

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmf3F4echKqOuFEgEQJUTwCg9WZH5kHhrssgusIk+dOrjHV8F7cAnAvl
1WM18Tt3jV8sJkJjuGcvA1bE
=TYJ3
-----END PGP SIGNATURE-----
 
Back
Top