CODEWITHSUNDEEP
sqlvb.netms-accessjet
Garrett
Garrett

Reputation: 55

SQL String Troubles with Multiple Functions

I am using a local Access Database connected to Visual Basic. My query is

SELECT RebateReceived, DatePart('yyyy',[RebateMailedDate]) AS MailedDate, Sum(RebateValue) as MoneyReceived
    FROM RebateInfoStorage
    where RebateReceived='Received'
    group by RebateReceived
    having DatePart('yyyy',[RebateMailedDate])

I am trying to get the columns that have the same year and the word(s) that have 'received' to identify the records that need to be summed (Added) together. I am not very familiar with the Group By and Having keywords or the Sum() and DatePart() functions.

Upvotes: 1

Views: 52

Answers (2)

Ivan Ling
Ivan Ling

Reputation: 117

You should group by DatePart having RebateReceived='Received'. For more information about the syntax of Having you may refer to http://www.w3schools.com/sql/sql_having.asp

Group by means your output table will be grouped according to unique elements in that column. For example, if there are multiple entry with 2014 as year, they will all be grouped together, and their RebateValue will be added up together. If you are grouping with RebateReceived, all the entry will be added and you will end up with a single sum.

Upvotes: 0

Luke
Luke

Reputation: 1724

So the DBMS will go into the RebateInfoStorage and grab all the rows where RebateReceived = 'Received'. Then, it'll group those records, where each group contains records where the expression DatePart('yyyy', RebateMailedDate) evaluates to the same value (i.e. they have the same year). Then for each group, it'll return a single result row with the year, and the sum of all the RebateValues in that group. Operations happen in that order.

HAVING is like WHERE, but happens after the GROUP BY and is a condition placed on a group of records, whereas WHERE is a condition on a record.

SELECT
  YEAR(RebateMailedDate) AS MailedDate,
  SUM(RebateValue) as MoneyReceived
FROM
  RebateInfoStorage
WHERE
  RebateReceived = 'Received'
GROUP BY
  YEAR(RebateMailedDate);

EDIT: It would appear that YEAR(x) is a more appropriate function!

Upvotes: 1

Related Questions