I have Two tables. Products and ProductInfo.
All the Products(productId) have entries in ProductInfo (FK productId)
I want to find out products whose entries do not exist in ProductInfo
Pls hlpSELECT ProductID --, ...
FROM Products p
LEFT OUTER JOIN ProductInfo pi
ON p.ProductID = pi.ProductID
WHERE pi.ProductID IS NULL
-- or
SELECT ProductID --, ...
FROM Products
WHERE ProductID NOT IN
(
SELECT ProductID
FROM ProductInfo
)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
>I have Two tables. Products and ProductInfo.
> All the Products(productId) have entries in ProductInfo (FK productId)
> I want to find out products whose entries do not exist in ProductInfo
> Pls hlp|||Hi
SELECT <column lists> FROM Products WHERE NOT EXISTS
(SELECT * FROM ProductInfo WHERE Products.ProductId=ProductInfo.Productid )
"mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
> I have Two tables. Products and ProductInfo.
> All the Products(productId) have entries in ProductInfo (FK productId)
> I want to find out products whose entries do not exist in ProductInfo
> Pls hlp|||SELECT ...
FROM Product AS p
WHERE NOT EXISTS(SELECT * FROM ProductInfo AS pi WHERE pi.productID = p.Prod
uctId)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
>I have Two tables. Products and ProductInfo.
> All the Products(productId) have entries in ProductInfo (FK productId)
> I want to find out products whose entries do not exist in ProductInfo
> Pls hlp|||Hi , Aaron
> SELECT ProductID --, ...
> FROM Products
> WHERE ProductID NOT IN
> (
> SELECT ProductID
> FROM ProductInfo
> )
>
I have my doubt about this query because if the OP has ProductId IS NULL
we may get back a wrong output .Am I right
BTW why did you drop an acoount (ng) ? :-)
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:ux6n8fLUFHA.1796@.TK2MSFTNGP15.phx.gbl...
> SELECT ProductID --, ...
> FROM Products p
> LEFT OUTER JOIN ProductInfo pi
> ON p.ProductID = pi.ProductID
> WHERE pi.ProductID IS NULL
> -- or
> SELECT ProductID --, ...
> FROM Products
> WHERE ProductID NOT IN
> (
> SELECT ProductID
> FROM ProductInfo
> )
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> "mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
> news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
>|||> I have my doubt about this query because if the OP has ProductId IS NULL
> we may get back a wrong output .Am I right
Possibly, but it was defined as a foreign key, so I figured the chance of it
being NULL was pretty small.
> BTW why did you drop an acoount (ng) ? :-)
Because there are a couple of dimwits in here who don't seem to know when to
shut up. Not you, of course.
A|||Yep, I sow a few posts. Sometimes it is really sorry that this NG is
unmoderated , should be a moderator here.
"AB - MVP" <ten.xoc@.dnartreb.noraa> wrote in message
news:egwAZpLUFHA.2892@.TK2MSFTNGP14.phx.gbl...
NULL
> Possibly, but it was defined as a foreign key, so I figured the chance of
it
> being NULL was pretty small.
>
> Because there are a couple of dimwits in here who don't seem to know when
to
> shut up. Not you, of course.
> A
>|||I agree with AB. Some people in the newsgroup dont know how to respect the
SQL MVPs. They contradict them, argue with them for no apparent reason and
talk to them in a demeaning way. I am not a MVP but I personally think
everyone should respect all the SQL MVPs because of their tremendous
knowledge and experience which they share to perform better in our jobs.
They give great design solutions, great answers and then actually point the
flaws when we are doing something wrong.
I read all of MVP posts and I always follow and implement their suggestions.
I am hoping others would learn to respect them.
Thanks
M
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eEwG7gLUFHA.3840@.tk2msftngp13.phx.gbl...
> SELECT ...
> FROM Product AS p
> WHERE NOT EXISTS(SELECT * FROM ProductInfo AS pi WHERE pi.productID =
p.ProductId)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "mavrick101" <mavrick101@.discussions.microsoft.com> wrote in message
> news:4939AE3D-7B71-418F-A6E6-8AD4DBD56A8F@.microsoft.com...
>
Showing posts with label entries. Show all posts
Showing posts with label entries. Show all posts
Friday, March 30, 2012
Friday, March 23, 2012
Query for most recent of duplicate records
I need some ideas on this query.
I have a table with entries similar to the following with columns name,
id, and timestamp.
kmyoung 345 2005-08-22 07:29:00.000
kmyoung 345 2005-08-29 07:29:15.000
mphillips 360 2005-08-27 14:48:18.000
rbeheler 360 2005-08-22 09:29:11.000
rbeheler 360 2005-08-24 09:28:19.000
rbeheler 360 2005-08-29 09:27:54.000
I need a resultant set that gives me the records with the most recent
timestamp for each ID as listed below.
kmyoung 345 2005-08-29 07:29:15.000
rbeheler 360 2005-08-29 09:27:54.000
Thanks for the help.Try,
select
*
from
t1 as a
where
c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
go
AMB
"Jeff" wrote:
> I need some ideas on this query.
> I have a table with entries similar to the following with columns name,
> id, and timestamp.
> kmyoung 345 2005-08-22 07:29:00.000
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-22 09:29:11.000
> rbeheler 360 2005-08-24 09:28:19.000
> rbeheler 360 2005-08-29 09:27:54.000
> I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below.
> kmyoung 345 2005-08-29 07:29:15.000
> rbeheler 360 2005-08-29 09:27:54.000
> Thanks for the help.
>|||select name, id, max(timestamp) as timestamp
from thetable
group by name, id
having count(*)>1 -- if you need just the ones that have dupes, add this
line
Jeff wrote:
> I need some ideas on this query.
> I have a table with entries similar to the following with columns name,
> id, and timestamp.
> kmyoung 345 2005-08-22 07:29:00.000
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-22 09:29:11.000
> rbeheler 360 2005-08-24 09:28:19.000
> rbeheler 360 2005-08-29 09:27:54.000
> I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below.
> kmyoung 345 2005-08-29 07:29:15.000
> rbeheler 360 2005-08-29 09:27:54.000
> Thanks for the help.
>|||It worked great as long as t1 was an actual table. But in actuality t1 is a
union of two tables. When I substitute (select * from t1 union select * fro
m
t2) as t1, it no longer works. Would it be possible to rewrite this with a
subquery instead of t1?
"Alejandro Mesa" wrote:
> Try,
> select
> *
> from
> t1 as a
> where
> c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
> go
>
> AMB
> "Jeff" wrote:
>|||Very close, but I ended up with this resultant set instead.
kmyoung 345 2005-08-29 07:29:15.000
mphillips 360 2005-08-27 14:48:18.000
rbeheler 360 2005-08-29 09:27:54.000
I ended up with two entries for id 360.
"Trey Walpole" wrote:
> select name, id, max(timestamp) as timestamp
> from thetable
> group by name, id
> having count(*)>1 -- if you need just the ones that have dupes, add this
> line
>
> Jeff wrote:
>|||Nevermind. It worked fine by simply substituting the subquery in place of
t1. Works exactly as I need it to .
Thanks!
"Jeff" wrote:
> It worked great as long as t1 was an actual table. But in actuality t1 is
a
> union of two tables. When I substitute (select * from t1 union select * f
rom
> t2) as t1, it no longer works. Would it be possible to rewrite this with
a
> subquery instead of t1?
> "Alejandro Mesa" wrote:
>|||oops - seeing a little cross-eyed today...
Jeff wrote:
> Very close, but I ended up with this resultant set instead.
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-29 09:27:54.000
> I ended up with two entries for id 360.
> "Trey Walpole" wrote:
>
I have a table with entries similar to the following with columns name,
id, and timestamp.
kmyoung 345 2005-08-22 07:29:00.000
kmyoung 345 2005-08-29 07:29:15.000
mphillips 360 2005-08-27 14:48:18.000
rbeheler 360 2005-08-22 09:29:11.000
rbeheler 360 2005-08-24 09:28:19.000
rbeheler 360 2005-08-29 09:27:54.000
I need a resultant set that gives me the records with the most recent
timestamp for each ID as listed below.
kmyoung 345 2005-08-29 07:29:15.000
rbeheler 360 2005-08-29 09:27:54.000
Thanks for the help.Try,
select
*
from
t1 as a
where
c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
go
AMB
"Jeff" wrote:
> I need some ideas on this query.
> I have a table with entries similar to the following with columns name,
> id, and timestamp.
> kmyoung 345 2005-08-22 07:29:00.000
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-22 09:29:11.000
> rbeheler 360 2005-08-24 09:28:19.000
> rbeheler 360 2005-08-29 09:27:54.000
> I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below.
> kmyoung 345 2005-08-29 07:29:15.000
> rbeheler 360 2005-08-29 09:27:54.000
> Thanks for the help.
>|||select name, id, max(timestamp) as timestamp
from thetable
group by name, id
having count(*)>1 -- if you need just the ones that have dupes, add this
line
Jeff wrote:
> I need some ideas on this query.
> I have a table with entries similar to the following with columns name,
> id, and timestamp.
> kmyoung 345 2005-08-22 07:29:00.000
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-22 09:29:11.000
> rbeheler 360 2005-08-24 09:28:19.000
> rbeheler 360 2005-08-29 09:27:54.000
> I need a resultant set that gives me the records with the most recent
> timestamp for each ID as listed below.
> kmyoung 345 2005-08-29 07:29:15.000
> rbeheler 360 2005-08-29 09:27:54.000
> Thanks for the help.
>|||It worked great as long as t1 was an actual table. But in actuality t1 is a
union of two tables. When I substitute (select * from t1 union select * fro
m
t2) as t1, it no longer works. Would it be possible to rewrite this with a
subquery instead of t1?
"Alejandro Mesa" wrote:
> Try,
> select
> *
> from
> t1 as a
> where
> c3 = (select max(b.c3) from t1 as b where b.[id] = a.[id])
> go
>
> AMB
> "Jeff" wrote:
>|||Very close, but I ended up with this resultant set instead.
kmyoung 345 2005-08-29 07:29:15.000
mphillips 360 2005-08-27 14:48:18.000
rbeheler 360 2005-08-29 09:27:54.000
I ended up with two entries for id 360.
"Trey Walpole" wrote:
> select name, id, max(timestamp) as timestamp
> from thetable
> group by name, id
> having count(*)>1 -- if you need just the ones that have dupes, add this
> line
>
> Jeff wrote:
>|||Nevermind. It worked fine by simply substituting the subquery in place of
t1. Works exactly as I need it to .
Thanks!
"Jeff" wrote:
> It worked great as long as t1 was an actual table. But in actuality t1 is
a
> union of two tables. When I substitute (select * from t1 union select * f
rom
> t2) as t1, it no longer works. Would it be possible to rewrite this with
a
> subquery instead of t1?
> "Alejandro Mesa" wrote:
>|||oops - seeing a little cross-eyed today...
Jeff wrote:
> Very close, but I ended up with this resultant set instead.
> kmyoung 345 2005-08-29 07:29:15.000
> mphillips 360 2005-08-27 14:48:18.000
> rbeheler 360 2005-08-29 09:27:54.000
> I ended up with two entries for id 360.
> "Trey Walpole" wrote:
>
Wednesday, March 21, 2012
Query Filtering Entries
Here's one for you guys:
I have a table with sales entries in it. To make things simple, I will put it this way:
There is an entry in the table for every item scanned. There may be some items that get voided out, but there will still be an entry in the table. No problem for the one transaction that was voided, because there is a negative dollar amount which I've already filtered out easily. However, there will still be an entry in the table for the same Item that got voided with a positive dollar amount. Make sense? I want to filter out the one entry with the positive dollar amount because it's not really a sale. It was voided in another entry in the table... So, If I come to the store, and buy a pack of gum for $0.99. The cashier scans it, then I decide I don't want it, so the cashier voids it off. I've got two entries in the table for that pack of gum. One for $0.99 where the cashier scanned, and one for -$0.99 where the cashier voided it off. I've got to get rid of the positive transaction.
Too confusing?Any chance you could add a column to use as a void flag? This would
make filtering this item much less cumbersome...|||nope. I can't make structural changes like that.|||Originally posted by AnSQLQuery
Here's one for you guys:
I have a table with sales entries in it. To make things simple, I will put it this way:
There is an entry in the table for every item scanned. There may be some items that get voided out, but there will still be an entry in the table. No problem for the one transaction that was voided, because there is a negative dollar amount which I've already filtered out easily. However, there will still be an entry in the table for the same Item that got voided with a positive dollar amount. Make sense? I want to filter out the one entry with the positive dollar amount because it's not really a sale. It was voided in another entry in the table... So, If I come to the store, and buy a pack of gum for $0.99. The cashier scans it, then I decide I don't want it, so the cashier voids it off. I've got two entries in the table for that pack of gum. One for $0.99 where the cashier scanned, and one for -$0.99 where the cashier voided it off. I've got to get rid of the positive transaction.
Too confusing?
Not too confusing...but it will be easier if you are running this through several tables and views.
Assuming that the transactions have a date time stamp and/or indexing column on it, and item number you could work it this way
Assuming that the sales_table is something like this
Index Item_Number Sales_Amt .....
Make a view called voided_sales which just sees the voided transactions and the sales_amt*-1
Insert into Voided_Indexes
Select max(index)
From sales_table
group by index
having index < (select index from sales_table, voided_sales
where sales_table.sales_Amt = voided_sales.Sales_Amt
and sales_table.Item_Number = voided_sales.Item_Number)
Once you get the voided_indexes list built then your query becomes
Select *
From sales_table
where index not in (select index from voided_sales)
and index not in (select index from voided_Indexes)
There are probably more efficient ways of doing it, but this is a quick scratch off the top of my head.|||select * from sales where saleAmt not in (select saleAmt * -1 from sales1)
The subquery may not make it efficient but this is probably the simplest
form of query...|||Originally posted by rocket39
select * from sales where saleAmt not in (select saleAmt * -1 from sales1)
The subquery may not make it efficient but this is probably the simplest
form of query...
The problem with that is that if the item subsequently sells it will be blocked out as well as the valid void.|||Originally posted by jimpen
The problem with that is that if the item subsequently sells it will be blocked out as well as the valid void.
Yep, I guess I was assuming that there would be some piece of information that would relate the void to the sale transaction.|||It just seems like there should be a more simple solution. (I haven't exactly figured out the creating a view suggestion just yet.)
It seems like i should be able to say if there is an entry that matches this TransactionID # and this dollar amt*-1 within the query, so that I'm using whatever transaction Id the query is on at that moment..don't count it. :) I know that is kind of funny sounding, but ...|||Originally posted by AnSQLQuery
It just seems like there should be a more simple solution. (I haven't exactly figured out the creating a view suggestion just yet.)
It seems like i should be able to say if there is an entry that matches this TransactionID # and this dollar amt*-1 within the query, so that I'm using whatever transaction Id the query is on at that moment..don't count it. :) I know that is kind of funny sounding, but ...
Create or Replace View voided_sales as Select Index, Item_Number, (Sales_Amt*-1) As Voided_Sales_Amt, ... or something similar...I've been doing more Oracle this week than MSSQL.
I rushed it should be
sales_table.sales_Amt = voided_sales.Voided_Sales_Amt
The reason for the view is trying to keep multiple recursive queries straight is a royal PITA. The reason for the max index is to get the most recent sale to match the return. That is not a guarentee match to the sale, but it will at least get it out of the totals.|||This just came to me, and I don't know why I didn't think of it before, but I would think that I could just get rid of the filter that takes out the voided transactions all together.
Since the voided transactions have a negative dollar amount, when I do a SUM() on the Dollars the negative amounts will cancel out, and The dollar figure will be correct. (I think anyway)
As for the number of sales, all I have to do is count the number of voided transactions, and subtract that amount from the total number of sales.
I'll let you know if this works...|||you can use self join for that, join the table with itself using the product id, price (with different signs) to get the records you want. thats the most simple - elegant way i can think of though im not sure how hot it is performance wise.
consider flaggnig voided records when inserting the negative valued record using either a trigger or (much better) in the sproc performing the insertion.
Good luck
I have a table with sales entries in it. To make things simple, I will put it this way:
There is an entry in the table for every item scanned. There may be some items that get voided out, but there will still be an entry in the table. No problem for the one transaction that was voided, because there is a negative dollar amount which I've already filtered out easily. However, there will still be an entry in the table for the same Item that got voided with a positive dollar amount. Make sense? I want to filter out the one entry with the positive dollar amount because it's not really a sale. It was voided in another entry in the table... So, If I come to the store, and buy a pack of gum for $0.99. The cashier scans it, then I decide I don't want it, so the cashier voids it off. I've got two entries in the table for that pack of gum. One for $0.99 where the cashier scanned, and one for -$0.99 where the cashier voided it off. I've got to get rid of the positive transaction.
Too confusing?Any chance you could add a column to use as a void flag? This would
make filtering this item much less cumbersome...|||nope. I can't make structural changes like that.|||Originally posted by AnSQLQuery
Here's one for you guys:
I have a table with sales entries in it. To make things simple, I will put it this way:
There is an entry in the table for every item scanned. There may be some items that get voided out, but there will still be an entry in the table. No problem for the one transaction that was voided, because there is a negative dollar amount which I've already filtered out easily. However, there will still be an entry in the table for the same Item that got voided with a positive dollar amount. Make sense? I want to filter out the one entry with the positive dollar amount because it's not really a sale. It was voided in another entry in the table... So, If I come to the store, and buy a pack of gum for $0.99. The cashier scans it, then I decide I don't want it, so the cashier voids it off. I've got two entries in the table for that pack of gum. One for $0.99 where the cashier scanned, and one for -$0.99 where the cashier voided it off. I've got to get rid of the positive transaction.
Too confusing?
Not too confusing...but it will be easier if you are running this through several tables and views.
Assuming that the transactions have a date time stamp and/or indexing column on it, and item number you could work it this way
Assuming that the sales_table is something like this
Index Item_Number Sales_Amt .....
Make a view called voided_sales which just sees the voided transactions and the sales_amt*-1
Insert into Voided_Indexes
Select max(index)
From sales_table
group by index
having index < (select index from sales_table, voided_sales
where sales_table.sales_Amt = voided_sales.Sales_Amt
and sales_table.Item_Number = voided_sales.Item_Number)
Once you get the voided_indexes list built then your query becomes
Select *
From sales_table
where index not in (select index from voided_sales)
and index not in (select index from voided_Indexes)
There are probably more efficient ways of doing it, but this is a quick scratch off the top of my head.|||select * from sales where saleAmt not in (select saleAmt * -1 from sales1)
The subquery may not make it efficient but this is probably the simplest
form of query...|||Originally posted by rocket39
select * from sales where saleAmt not in (select saleAmt * -1 from sales1)
The subquery may not make it efficient but this is probably the simplest
form of query...
The problem with that is that if the item subsequently sells it will be blocked out as well as the valid void.|||Originally posted by jimpen
The problem with that is that if the item subsequently sells it will be blocked out as well as the valid void.
Yep, I guess I was assuming that there would be some piece of information that would relate the void to the sale transaction.|||It just seems like there should be a more simple solution. (I haven't exactly figured out the creating a view suggestion just yet.)
It seems like i should be able to say if there is an entry that matches this TransactionID # and this dollar amt*-1 within the query, so that I'm using whatever transaction Id the query is on at that moment..don't count it. :) I know that is kind of funny sounding, but ...|||Originally posted by AnSQLQuery
It just seems like there should be a more simple solution. (I haven't exactly figured out the creating a view suggestion just yet.)
It seems like i should be able to say if there is an entry that matches this TransactionID # and this dollar amt*-1 within the query, so that I'm using whatever transaction Id the query is on at that moment..don't count it. :) I know that is kind of funny sounding, but ...
Create or Replace View voided_sales as Select Index, Item_Number, (Sales_Amt*-1) As Voided_Sales_Amt, ... or something similar...I've been doing more Oracle this week than MSSQL.
I rushed it should be
sales_table.sales_Amt = voided_sales.Voided_Sales_Amt
The reason for the view is trying to keep multiple recursive queries straight is a royal PITA. The reason for the max index is to get the most recent sale to match the return. That is not a guarentee match to the sale, but it will at least get it out of the totals.|||This just came to me, and I don't know why I didn't think of it before, but I would think that I could just get rid of the filter that takes out the voided transactions all together.
Since the voided transactions have a negative dollar amount, when I do a SUM() on the Dollars the negative amounts will cancel out, and The dollar figure will be correct. (I think anyway)
As for the number of sales, all I have to do is count the number of voided transactions, and subtract that amount from the total number of sales.
I'll let you know if this works...|||you can use self join for that, join the table with itself using the product id, price (with different signs) to get the records you want. thats the most simple - elegant way i can think of though im not sure how hot it is performance wise.
consider flaggnig voided records when inserting the negative valued record using either a trigger or (much better) in the sproc performing the insertion.
Good luck
Wednesday, March 7, 2012
Query Confusion -- Please help
I have three categories (cat1, cat2, cat3) that are updated each month at different times.
Cat1 may have multiple entries for the month.
I need monthly totals for each category and a monthly grand total for all categories.
Here is my problem. We have to start with a certain balance and continue from there. The balance for the end of January is the beginning value for February.
SELECT Sum(Transactions.cat1) AS SumOfcat1, Sum(Transactions.cat3) AS SumOfcat3, Sum(Transactions.cat2) AS SumOfcat2, Transactions.monthID, (sumofcat1+sumofcat2+sumofcat3) AS MonthlyTotal
FROM Transactions
GROUP BY Transactions.monthID
ORDER BY Transactions.monthID;
The above query returns the following
SumofCat1 SumofCat2 SumofCat3 MonthID MonthlyTotal
-- -- -- -- --
90 0 50 1 140
58 9 108 2 175
50 100 100 3 250
I need another field called GrandTotal that shows as follows
GrandTotal
140
315
575
Please help. I looked at the http://www.databasejournal.com/featu...le.php/3112381 site with the grand total tutorial, but can't get it to work in access.
hi jason,
Try following query:
select
SumOfcat1, SumOfcat3, SumOfcat2, monthID , (sumofcat1+sumofcat2+sumofcat3)
AS MonthlyTotal ,
(select sum(cat1)+sum(cat2)+sum(cat3)
from transactions a
where a.monthid <= x.monthid ) 'grandtotal'
from
(SELECT Sum(Transactions.cat1) AS SumOfcat1, Sum(Transactions.cat3) AS
SumOfcat3,
Sum(Transactions.cat2) AS SumOfcat2, monthID
FROM Transactions
GROUP BY monthID)X
ORDER BY monthID;
Vishal Parkar
vgparkar@.yahoo.co.in
Cat1 may have multiple entries for the month.
I need monthly totals for each category and a monthly grand total for all categories.
Here is my problem. We have to start with a certain balance and continue from there. The balance for the end of January is the beginning value for February.
SELECT Sum(Transactions.cat1) AS SumOfcat1, Sum(Transactions.cat3) AS SumOfcat3, Sum(Transactions.cat2) AS SumOfcat2, Transactions.monthID, (sumofcat1+sumofcat2+sumofcat3) AS MonthlyTotal
FROM Transactions
GROUP BY Transactions.monthID
ORDER BY Transactions.monthID;
The above query returns the following
SumofCat1 SumofCat2 SumofCat3 MonthID MonthlyTotal
-- -- -- -- --
90 0 50 1 140
58 9 108 2 175
50 100 100 3 250
I need another field called GrandTotal that shows as follows
GrandTotal
140
315
575
Please help. I looked at the http://www.databasejournal.com/featu...le.php/3112381 site with the grand total tutorial, but can't get it to work in access.
hi jason,
Try following query:
select
SumOfcat1, SumOfcat3, SumOfcat2, monthID , (sumofcat1+sumofcat2+sumofcat3)
AS MonthlyTotal ,
(select sum(cat1)+sum(cat2)+sum(cat3)
from transactions a
where a.monthid <= x.monthid ) 'grandtotal'
from
(SELECT Sum(Transactions.cat1) AS SumOfcat1, Sum(Transactions.cat3) AS
SumOfcat3,
Sum(Transactions.cat2) AS SumOfcat2, monthID
FROM Transactions
GROUP BY monthID)X
ORDER BY monthID;
Vishal Parkar
vgparkar@.yahoo.co.in
Subscribe to:
Posts (Atom)