SQL Select statement multiple rows

Question

I have 2 tables as follows.

Project table.

ID      ProjectID      description      etc. 
1       4849           aaabbbb          etc.

Invoice table.

ID      ProjectID      InvoiceNo      InvoiceDate      amount
1       4898           1234           01.01.01         200
2       4898           5678           02.02.02         475

What I need is the following.

ProjectID      Description      Invoicedate      amount
4898           aaabbbb          02.02.02         675

I think I am over thinking it but need help.

most tables have multiple rows. So your title is not really informative — t-clausen.dk
– t-clausen.dk, Commented Feb 14, 2013 at 20:42

paddy · Accepted Answer · 2013-02-14 20:43:42Z

2

Use grouping with a sum:

SELECT i.projectId, description, MAX(invoiceDate), SUM(amount)
   FROM invoice i
   JOIN project p ON p.projectId = i.projectId
   GROUP BY i.projectId;

I elected to use max to get the latest invoice date for each project. Assuming that is what you wanted.

edited Feb 14, 2013 at 20:43

answered Feb 14, 2013 at 20:38

paddy

63.7k7 gold badges68 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

zimdanen · Accepted Answer · 2013-02-14 21:08:31Z

1

Try something like this:

SELECT Project.ProjectID, Description, MAX(InvoiceDate), SUM(Amount)
FROM Project JOIN Invoice ON Project.ProjectID = Invoice.ProjectID
GROUP BY Project.ProjectID, Description

If your InvoiceDate isn't an actual DATETIME, you will need to convert it for this to work or use another way of getting max of InvoiceDate.

SQL Fiddle here. Note that I changed the ProjectIDs in your Invoice table to match the one in your Invoice table.

edited Feb 14, 2013 at 21:08

answered Feb 14, 2013 at 20:38

zimdanen

5,6367 gold badges47 silver badges96 bronze badges

5 Comments

Hypocraties Over a year ago

When i do max invoicedate it just returns #Name and returns two rows still

Hypocraties Over a year ago

Sorry forgot to mention invoicedate is a datetime

zimdanen Over a year ago

The value for MAX(InvoiceDate) is #Name? Are you sure that InvoiceDate is a DATETIME, then? Also, can you post the exact query and results?

Hypocraties Over a year ago

Its really wierd but it is set as a datime field when i look at the table desin but still comes back as #Name? Any ideas but thanks to your input it has now become one single row

Hypocraties Over a year ago

Sorted it, me being a muppet had not selected invoicedate as an alias

scones · Accepted Answer · 2013-02-14 20:37:55Z

0

select
  *,
  sum(amount) as amount
from
  Project as p
  inner join Invoice as i
    on p.ProjectID = i.ProjectID
group by ProjectID

the invoice date may vary.

answered Feb 14, 2013 at 20:37

scones

3,35526 silver badges36 bronze badges

Comments

mtougeron · Accepted Answer · 2013-02-14 20:40:40Z

0

I'm not entirely sure how you want to do the grouping, but it could be something like

SELECT project.ProjectID, project.description, MAX(invoice.InvoiceDate), SUM(invoice.amount) FROM project JOIN invoice ON project.ProjectID = invoice.ProjectID WHERE project.ProjectID = 4898 GROUP BY project.ProjectID

answered Feb 14, 2013 at 20:40

mtougeron

812 bronze badges

Collectives™ on Stack Overflow

SQL Select statement multiple rows

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related