By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,940 Members | 1,391 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,940 IT Pros & Developers. It's quick & easy.

distict and sum

P: n/a
Hi
i have a table that contains several fields

field 1(area), field2(quantity),Field3(price)
What i want to do is
return total quatity and total price against a distict area

i have the following code

strSQL = "SELECT distinctrow datatable.area,
Sum([DataTable].TotalPrice) AS [SumOfTotal Price],
Sum([DataTable].Quantity) AS [SumOfTotal Quantity]" & _
"FROM datatable " & _
"WHERE DataTable.Dates Between" &
SQLDate(Me.cboStartDate.Value) & "And" & SQLDate(cboEndDate.Value) & _
"GROUP BY DataTable.Area, DataTable.TotalPrice,
DataTable.Dates, DataTable.Quantity "
this code returns each record in the table
ie
area Quantity Total Price
office 10 400
gym 20 100
office 50 2000

What i want is
area Quantity Total Price
office 60 2400
gym 20 100
i can not figure out how to return the distinct area with the totals

any help greatly welcomed

thanks

kevin
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi Kevin,

There's no need for distinctrow for the type of query you're using but to
get the result you want only GROUP BY the fields that you're not using the
Sum function on.
GROUP BY DataTable.Area; <== This is all that's needed. Remove the other 3
fields from this clause

Stewart
"Kevin Carter" <kc******@ford.com> wrote in message
news:2a**************************@posting.google.c om...
Hi
i have a table that contains several fields

field 1(area), field2(quantity),Field3(price)
What i want to do is
return total quatity and total price against a distict area

i have the following code

strSQL = "SELECT distinctrow datatable.area,
Sum([DataTable].TotalPrice) AS [SumOfTotal Price],
Sum([DataTable].Quantity) AS [SumOfTotal Quantity]" & _
"FROM datatable " & _
"WHERE DataTable.Dates Between" &
SQLDate(Me.cboStartDate.Value) & "And" & SQLDate(cboEndDate.Value) & _
"GROUP BY DataTable.Area, DataTable.TotalPrice,
DataTable.Dates, DataTable.Quantity "
this code returns each record in the table
ie
area Quantity Total Price
office 10 400
gym 20 100
office 50 2000

What i want is
area Quantity Total Price
office 60 2400
gym 20 100
i can not figure out how to return the distinct area with the totals

any help greatly welcomed

thanks

kevin

Nov 13 '05 #2

P: n/a
"Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message news:<cc**********@news.wave.co.nz>...
Hi Kevin,

There's no need for distinctrow for the type of query you're using but to
get the result you want only GROUP BY the fields that you're not using the
Sum function on.
GROUP BY DataTable.Area; <== This is all that's needed. Remove the other 3
fields from this clause

Stewart
"Kevin Carter" <kc******@ford.com> wrote in message
news:2a**************************@posting.google.c om...
Hi
i have a table that contains several fields

field 1(area), field2(quantity),Field3(price)
What i want to do is
return total quatity and total price against a distict area

i have the following code

strSQL = "SELECT distinctrow datatable.area,
Sum([DataTable].TotalPrice) AS [SumOfTotal Price],
Sum([DataTable].Quantity) AS [SumOfTotal Quantity]" & _
"FROM datatable " & _
"WHERE DataTable.Dates Between" &
SQLDate(Me.cboStartDate.Value) & "And" & SQLDate(cboEndDate.Value) & _
"GROUP BY DataTable.Area, DataTable.TotalPrice,
DataTable.Dates, DataTable.Quantity "
this code returns each record in the table
ie
area Quantity Total Price
office 10 400
gym 20 100
office 50 2000

What i want is
area Quantity Total Price
office 60 2400
gym 20 100
i can not figure out how to return the distinct area with the totals

any help greatly welcomed

thanks

kevin


thanks for reply

When i run the query i get a message box asking for the date. this i
want to avoid as the user has already selected the date range. if i
include the dates in my select statement it becomes part of the
distinct criteria, What i need is a distinct area with all the dates
in the range selected by the user

Hope this makes sense
kevin
Nov 13 '05 #3

P: n/a
"Kevin Carter" <kc******@ford.com> wrote in message
news:2a**************************@posting.google.c om...
"Stewart Allen" <sa****@NOT.wave.THIS.co.nz> wrote in message

news:<cc**********@news.wave.co.nz>...
Hi Kevin,

There's no need for distinctrow for the type of query you're using but to get the result you want only GROUP BY the fields that you're not using the Sum function on.
GROUP BY DataTable.Area; <== This is all that's needed. Remove the other 3 fields from this clause

Stewart
"Kevin Carter" <kc******@ford.com> wrote in message
news:2a**************************@posting.google.c om...
Hi
i have a table that contains several fields

field 1(area), field2(quantity),Field3(price)
What i want to do is
return total quatity and total price against a distict area

i have the following code

strSQL = "SELECT distinctrow datatable.area,
Sum([DataTable].TotalPrice) AS [SumOfTotal Price],
Sum([DataTable].Quantity) AS [SumOfTotal Quantity]" & _
"FROM datatable " & _
"WHERE DataTable.Dates Between" &
SQLDate(Me.cboStartDate.Value) & "And" & SQLDate(cboEndDate.Value) & _
"GROUP BY DataTable.Area, DataTable.TotalPrice,
DataTable.Dates, DataTable.Quantity "
this code returns each record in the table
ie
area Quantity Total Price
office 10 400
gym 20 100
office 50 2000

What i want is
area Quantity Total Price
office 60 2400
gym 20 100
i can not figure out how to return the distinct area with the totals

any help greatly welcomed

thanks

kevin


thanks for reply

When i run the query i get a message box asking for the date. this i
want to avoid as the user has already selected the date range. if i
include the dates in my select statement it becomes part of the
distinct criteria, What i need is a distinct area with all the dates
in the range selected by the user

Hope this makes sense
kevin


There's also a problem with your WHERE clause, use the # as the delimiter
for dates.
e.g.
"WHERE DataTable.Dates Between #" & SQLDate(Me.cboStartDate.Value) _
& "# And #" & SQLDate(cboEndDate.Value) _
& "GROUP BY DataTable.Area;"

There's no need for distinctrow if you are using the aggregate functions
(Sum, Count, Min etc.). Using your dates as the filter in your WHERE clause
will collect the required records for you.

e.g. DataTable
Area Quantity TotalPrice TheDate
office 10 400 16/06/04
gym 20 100 17/06/04
office 50 2000 20/06/04

SELECT Area, Sum(Quantity) AS Q, Sum(TotalPrice) AS P
FROM DataTable
WHERE TheDate Between #06/15/04# And #06/17/04#
GROUP BY Area;
Returns
Area Q P
office 10 400
gym 20 100

But
SELECT Area, Sum(Quantity) AS Q, Sum(TotalPrice) AS P
FROM DataTable
WHERE TheDate Between #06/15/04# And #06/20/04#
GROUP BY Area;
Returns
Area Q P
office 60 2400
gym 20 100
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.