CODEWITHSUNDEEP
sqlt-sqlms-access
Ruby Kousinovali
Ruby Kousinovali

Reputation: 347

Access SQL query group by

I have a table with TaxID, Price, DatePurchaced Every TaxId showning multiple times with different Price and DatePurchaced I want to Group by TaxId with the Sum(Price) and show the last DatePurchaced I also want a column with Count(TaxID) I tryied ome queries but always show me one record the one with the latest datePurchaced

Upvotes: 0

Views: 48

Answers (1)

Sebastian S.
Sebastian S.

Reputation: 1391

declare @tbl as table (TaxID int, Price money, DatePurchaced datetime)

insert into @tbl (TaxID, Price, DatePurchaced) values (1, 10.5, '2015-03-05 10:25:23');
insert into @tbl (TaxID, Price, DatePurchaced) values (1, 13, '2015-03-09 10:25:23');
insert into @tbl (TaxID, Price, DatePurchaced) values (1, 7, '2015-03-20 10:25:23');
insert into @tbl (TaxID, Price, DatePurchaced) values (2, 10.5, '2015-07-05 10:25:23');
insert into @tbl (TaxID, Price, DatePurchaced) values (2, 8, '2015-07-08 10:25:23');


select  t.TaxId as TaxID, 
        count(t.TaxId) as Cnt,
        sum(t.Price) as TotalPrice,
        max(t.DatePurchaced) as LastPurchaseDate
from @tbl as t
group by t.TaxId

Upvotes: 1

Related Questions