[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 150
  • Last Modified:

Aggregated Function from Access (First)

Hi

I need to know what the same function in SQL server 7 is for (First) the Aggregated function for Access.

e.g.

SELECT dtYear,dtMonth,
Sum(dtSales.dtBulkValue) AS dtBulkValue,
Sum(dtSales.dtBulkVolume) AS dtBulkVolume,
First(dtSales.dtRepCode) AS dtRepCode,
First(dtSales.dtTradeChannel) AS dtTradeChannel
FROM tblCustomers INNER JOIN dtSales
0
Rab
Asked:
Rab
1 Solution
 
AzraSoundCommented:
you may be able to do it with something like this:
SELECT TOP 1 dtYear,dtMonth,Sum(dtSales.dtBulkValue) AS dtBulkValue,
Sum(dtSales.dtBulkVolume) AS dtBulkVolume
FROM tblCustomers
INNER JOIN dtSales
ORDER BY (whichever field you want the top value for)


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
azrasound: using TOP 1 like this will produce 0 or 1 rows at all, you need to modify (see below)
Rab: you need to use GROUP BY:

SELECT S.dtYear,S.dtMonth,
Sum(S.dtBulkValue) AS dtBulkValue,
Sum(S.dtBulkVolume) AS dtBulkVolume,
( SELECT TOP 1 SI.dtRepCode FROM dtSales SI WHERE SI.dtYear = S.dtYear AND SI.dtMonth = S.dtMonth) AS dtRepCode,
( SELECT TOP 1 dtTradeChannel FROM dtSales SI WHERE SI.dtYear = S.dtYear AND SI.dtMonth = S.dtMonth) AS dtTradeChannel
FROM tblCustomers C INNER JOIN dtSales S
GROUP BY S.dtYear, S.dtMonth

0
 
RabAuthor Commented:
Hi angelIII
If I use the query as above the SQL query returns two more records that the
Access query. Why would this be.

query e.g.

SELECT dtYear,dtMonth,
Sum(dtSales.dtSingleValue) AS dtSingleValue,
Sum(dtSales.dtSingleVolume) AS dtSingleVolume,
Sum(dtSales.dtMultiValue) AS dtMultiValue,
Sum(dtSales.dtMultiVolume) AS dtMultiVolume,
Sum(dtSales.dtBulkValue) AS dtBulkValue,
Sum(dtSales.dtBulkVolume) AS dtBulkVolume,
( SELECT TOP 1 dtSales.dtRepCode FROM dtSales SI WHERE SI.dtYear = dtSales.dtYear AND dtSales.dtMonth = dtSales.dtMonth) AS dtRepCode,
( SELECT TOP 1 dtTradeChannel FROM dtSales SI WHERE SI.dtYear = dtSales.dtYear AND dtSales.dtMonth = dtSales.dtMonth) AS dtTradeChannel

FROM tblCustomers  INNER JOIN dtSales
ON tblCustomers.CustomerID=dtSales.dtCustomerID
WHERE CNumber = '1000022120' Or CNumber = '1000022130'
GROUP BY dtYear,dtMonth,dtRepCode,dtTradeChannel
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now