A little help with a query

Question

Post reply

A little help with a query

jcharrasqueado

SSC Journeyman

Points: 95
More actions
August 29, 2010 at 8:49 pm

#227042

I have a dataset like:
InvoiceNumber InvoiceDate PieceSalePrice
123456 10/10/2009 2500.00
123456 10/10/2009 3600.00
How do I query it so the results look like:
InvoiceNumber InvoiceDate InvoiceTotal
123456 10/10/2009 6100.00
This means to sum the PieceSalePrice on all InvoiceNumber with same InvoiceDate
Tried several things but no go
Any help would be appreciated
Thanks
Tom

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Dint SUM and GROUP BY help ?

Like ?

selelct invoicenumber, invoicedate, sum(invoice) invoicetotal

from table

group by invoicenumber, invoicedate

jcharrasqueado SSC Journeyman Points: 95 More actions · Answer 2

That worked, I swear I tried it but I was wrong in the order at the group clause, I was grouping by the PieceSalePrice first instead of the InvoiceNumber.

This is my error:

Select

InvoiceNumber,

DateSale,

Sum(ArtPiecePrice) As InvoiceTotal

from Invoices

Where InvoiceNumber = '0173'

Group By ArtPiecePrice,InvoiceNumber,DateSale

Thanks a lot

Tom

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

jcharrasqueado (8/29/2010)
That worked, I swear I tried it

It happens, Tom.. More than the order at the GROUP BY clause, you perhaps needed some more coffee 😀

jcharrasqueado SSC Journeyman Points: 95 More actions · Answer 4

ColdCoffee (8/29/2010)
jcharrasqueado (8/29/2010)
That worked, I swear I tried it
It happens, Tom.. More than the order at the GROUP BY clause, you perhaps needed some more coffee 😀

Or maybe less coffee :), thanks again, it worked like a charm.

Tom

WayneS SSC Guru Points: 95461 More actions · Answer 5

Tom, it actually sounds like you really needed some Cold Coffee :w00t:

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

WayneS (8/31/2010)
Tom, it actually sounds like you really needed some Cold Coffee :w00t:

LOL.. Yeah Wayne! :w00t: :hehe: