Help with aggregate query - Cant get it right!

Hi,

I have we have a client who gives their invoices in a flat file format, we import it into a SQL Server table.

Nothing is normalized – everything is repeated in every record. The fields are:

customerNumber
Invoice_number
PO_number
Qty
Description
Line_number
Line_total
Freight
Tax
Invoice_date

So an if an order has 10 line items, the header information (invoice number, PO number, ivoice date) are repeated on each of the lines

I am writing a query to show the following

Order number, Invoice total, Date

select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice_date from invoices group by invoice_no, Invoice, customerNumber

This works great - for each invoice I get the invoice number, InvoiceTotal, and Date

Then I was asked to add the PO Number – this is where I can't get it right.

When I added "PO_number" to the query, I got two lines for each invoice

select invoice_no, sum(line_total + freight + tax) as invoiceTotal, customerNumber, Invoice,PO_number from invoices group by invoice_no, Invoice, Sold_To_Cust_No,PO_number

Please help - I need to end up with: invoice_no, invoiceTotal, customerNumber, Invoice_date and PO_number (sequence does not matter)

Thanks

Hello my friend,

I see the problem. You want to get a field value without it causing other data to repeat. The solution is to use a function to get the one value and put this in the query like so: -

CREATE FUNCTION fn_GetPONumberByInvoice
(
@.InvoiceNumber AS VARCHAR(50) -- if it is an INT or whatever then change this line
)
RETURNS VARCHAR(80)

AS

BEGIN
DECLARE @.PONumber AS VARCHAR(80)

SET @.PONumber = (SELECT TOP 1 PO_Number FROM Invoices WHERE Invoice_Number = @.InvoiceNumber)

RETURN @.PONumber
END

Then in your query use the following: -

SELECT invoice_no,
sum(line_total + freight + tax) as invoiceTotal, customerNumber,
Invoice_date,
dbo.fn_GetPONumberByInvoice(invoice_no) AS 'PO_Number'
FROM invoices
GROUP BY invoice_no, Invoice, customerNumber

Kind regards

Scotty

|||

Scotty, thanks for your reply.

I am not well versed in SQL, so I have a question:

How can I use the query you wrote to create a view? I did not mention that earlier (sorry), but the objective is to create a view with header information for each invoice (invoice number, date, total amount and PO number)

Hope you can help.

Again, thanks.

|||

Hello again my friend,

You should be able to do it like this: -

CREATE VIEW vw_Invoices

AS

SELECT invoice_no,
sum(line_total + freight + tax) as invoiceTotal, customerNumber,
Invoice_date,
dbo.fn_GetPONumberByInvoice(invoice_no) AS 'PO_Number'
FROM invoices
GROUP BY invoice_no, Invoice, customerNumber

Kind regards

Scotty

|||

Scotty, THANK YOU.

Help with aggregate functions

I am new to SQL. I am trying to write a query that will join two tables. The join works fine, however I am getting incorrect results. I would like the joined tables to be grouped by ProposalId for the most current ProposalDate listing the ProposalAmount associated with the most current ProposalDate. I have listed the query, the result I get, and the result I would like to get. Any help or direction you could give me would be greatly appreciated.

The SQL script:

SELECT
p.ProposalId, p.ProjectManagerId, p.JobNumber, p.ClientName,
s.[Proposal Date], s.[Proposal Amount], s.[Times Submitted]
FROM
Proposals p join
(SELECT ProposalId 'ProposalId', ProposalAmount 'Proposal Amount', MAX(ProposalDate) 'Proposal Date', COUNT(ProposalId) 'Times Submitted'

FROM
SubmissionHistory
WHERE
ProjectManagerId = 1
GROUP BY
ProposalAmount, ProposalId) s on p.ProposalId = s.ProposalId

This is the result I get:

Proposal Project Job Proposal Proposal Times
Id ManagerId Number Client Date Amount Submitted

1 1 01004-001 SPS 2007-05-05 10000.00 1
2 1 01007-001 SAP2007-07-09 8000.00 1
2 1 01007-001 SAP2007-07-07 10000.00 1
4 1 01008-001 EAS2007-05-30 75000.00 1
4 1 01008-001 EAS2007-05-07 80000.00 1
4 1 01008-001 EAS2007-05-05 90000.00 1

This is the result I would like to get:

Proposal Project Job Proposal Proposal Times
Id ManagerId Number Client Date Amount Submitted

1 1 01004-001 SPS 2007-05-05 10000.00 1
2 1 01007-001 SAP2007-07-09 8000.00 2
4 1 01008-001 EAS2007-05-30 75000.00 3

Quote:

Originally Posted by yoyo35

I am new to SQL. I am trying to write a query that will join two tables. The join works fine, however I am getting incorrect results. I would like the joined tables to be grouped by ProposalId for the most current ProposalDate listing the ProposalAmount associated with the most current ProposalDate. I have listed the query, the result I get, and the result I would like to get. Any help or direction you could give me would be greatly appreciated.

The SQL script:

SELECT
p.ProposalId, p.ProjectManagerId, p.JobNumber, p.ClientName,
s.[Proposal Date], s.[Proposal Amount], s.[Times Submitted]
FROM
Proposals p join
(SELECT ProposalId 'ProposalId', ProposalAmount 'Proposal Amount', MAX(ProposalDate) 'Proposal Date', COUNT(ProposalId) 'Times Submitted'

FROM
SubmissionHistory
WHERE
ProjectManagerId = 1
GROUP BY
ProposalAmount, ProposalId) s on p.ProposalId = s.ProposalId

This is the result I get:

Proposal Project Job Proposal Proposal Times
Id ManagerId Number Client Date Amount Submitted

1 1 01004-001 SPS 2007-05-05 10000.00 1
2 1 01007-001 SAP2007-07-09 8000.00 1
2 1 01007-001 SAP2007-07-07 10000.00 1
4 1 01008-001 EAS2007-05-30 75000.00 1
4 1 01008-001 EAS2007-05-07 80000.00 1
4 1 01008-001 EAS2007-05-05 90000.00 1

This is the result I would like to get:

Proposal Project Job Proposal Proposal Times
Id ManagerId Number Client Date Amount Submitted

1 1 01004-001 SPS 2007-05-05 10000.00 1
2 1 01007-001 SAP2007-07-09 8000.00 2
4 1 01008-001 EAS2007-05-30 75000.00 3

Your core problem is having the ProposalAmount field in the inner query un-aggregated. Either remove it from there or use an aggregation function. I suggest that you reference to the amount in the outer query by proposal ID. (It is however a question how and if you want to have a single amount mixed with aggregated values (dates, times submitted) in the same line. If not, use aggregation inside.)

If you're really new to SQL this is more than decent even so far...|||Thank you for your reply. I appreciate your time. I will give it a shot and see what happens. Once again, thanks!!!!|||Hi i am not sure but i think you are trying to do a union join,, you should try and put in distinct in yout select statements so distinct(ID) for example

Help w/aggregate function in Matrix

On a report we have a matrix. The data cell has the following expression:
=iif(Fields!Score.Value=0, "", Fields!Score.Value)
When we run the report, we get the following warning:
The value expression for the textbox 'Score' references a field
outside an aggregate function. Value expressions in matrix cells should be
aggregates, to allow for subtotaling.
What does this mean and how can I resolve it?
The goal it to suppress the display of zero (0). We have tried setting the
format of the data cell to be "#", but the zero is still displayed. So we
have been using expressions like the above to achieve this.
The user creating this report is using the stand-alone C# IDE with Reporting
Services. This warning prevents them from previewing the report. Another
user using VS.NET 2003 is able to preview the report despite the warning.
The report renders on our test reporting server. If we need to just ignore
the warning, how can we get the user using C# to be able to preview the
report?
Thanks,
ChrisMatrix cells are always in the scope of two groupings and you could have
multiple data rows which match the group instance values. Therefore, you
should always use aggregate functions when referencing fields in a matrix
cell (hence, a processing warning gets generated).
If you don't use an explicit aggregate function in the matrix cell, we would
implicitly use the first row's field value. I believe you actually don't
want just the first value, but rather the sum - so you should change the
expression to:
=iif(Sum(Fields!Score.Value)=0, "", Sum(Fields!Score.Value))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
In your case, I believe you want
"Chris Walls" <chwalls@.community.nospam> wrote in message
news:OVqzX42OFHA.1500@.TK2MSFTNGP09.phx.gbl...
> On a report we have a matrix. The data cell has the following expression:
>
> =iif(Fields!Score.Value=0, "", Fields!Score.Value)
>
> When we run the report, we get the following warning:
>
> The value expression for the textbox 'Score' references a field
> outside an aggregate function. Value expressions in matrix cells should
> be aggregates, to allow for subtotaling.
>
>
> What does this mean and how can I resolve it?
>
>
> The goal it to suppress the display of zero (0). We have tried setting
> the format of the data cell to be "#", but the zero is still displayed.
> So we have been using expressions like the above to achieve this.
>
>
> The user creating this report is using the stand-alone C# IDE with
> Reporting Services. This warning prevents them from previewing the
> report. Another user using VS.NET 2003 is able to preview the report
> despite the warning. The report renders on our test reporting server. If
> we need to just ignore the warning, how can we get the user using C# to be
> able to preview the report?
>
>
> Thanks,
> Chris
>
>