CODEWITHSUNDEEP
sqlsql-server
S. Space
S. Space

Reputation: 315

SQL query to do this

I'm fairly new to SQL, but having trouble accomplishing this. I have a detailed invoice breakdown from a vendor, that I'm trying to format the following way:

**Account Name  |  Date  |  Cost**
ABC Company  |  2018-01-01  |  $2.60
ABC Company  |  2018-01-02  |  $3.10
ABC Company  |  2018-01-03  |  $0.90
ABC Company  |  2018-01-04  |  $1.42
DEF Company  |  2018-01-01  |  $2.19
DEF Company  |  2018-01-02  |  $8.10
DEF Company  |  2018-01-03  |  $2.91
DEF Company  |  2018-01-04  |  $4.19
FGH Company  |  2018-01-01  |  $1.10
FGH Company  |  2018-01-02  |  $9.10
FGH Company  |  2018-01-03  |  $3.48
FGH Company  |  2018-01-04  |  $1.91

The current invoice extract that I have (loaded into SQL), has several hundred items per account per day.

SO rather than doing that, I'd like to have an export for each day and each account, with the sum of the cost for that account and day

The format of the current table is something like:

ABC Company |  2018-01-01  |  $0.60
ABC Company |  2018-01-01  |  $0.75
ABC Company |  2018-01-01  |  $1.00
ABC Company |  2018-01-01  |  $0.25
ABC Company |  2018-01-02  |  $3.00
ABC Company |  2018-01-02  |  $0.07
ABC Company |  2018-01-02  |  $0.03
ABC Company |  2018-01-03  |  $0.40
ABC Company |  2018-01-03  |  $0.50

Hope that makes sense; i'm not sure how to go about it.

Upvotes: 1

Views: 65

Answers (1)

RToyo
RToyo

Reputation: 2877

You can use the GROUP BY clause to create one row per date and company, and then use SUM to get the sum of the costs. I don't know what your actual column/table names are, nor which DBMS you're using, but here's a basic demonstration:

SELECT
    [Account Name], 
    Date,
    Cost = SUM(cost)
FROM
    invoices
GROUP BY
    [Account Name], Date

Upvotes: 1

Related Questions