We help IT Professionals succeed at work.

Microsoft Access 2007 -- New SQL syntax for grouping by and counting

mmcgillo88
mmcgillo88 used Ask the Experts™
on
Please see my SQL query below:
SELECT inv.InvoiceNumber, inv.VendorName, inv.DateSubmitted, inv.DateCheckRequested, inv.IM_Check_Confirmed_Date, inv.NetItemAmount, DateDiff("d",[DateSubmitted],[DateCheckRequested]) AS PaymentTime, sum (inv.InvoiceNumber)
FROM tblInvoicesSubmitted AS inv
WHERE (((DateDiff("d",[DateSubmitted],[DateCheckRequested]))<=15) AND ((inv.[NetItemAmount])<=100))
GROUP BY inv.InvoiceNumber;

When I try to run the query it keeps say that Vendor Name or Date Submitted are not part of the aggregate function? Additionally, I want to group by invoice number so there are not repeats and then I want to count the number of unique invoice numbers after they have been grouped. Please advise on the appropriate SQL syntax. Appreciate the help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
Let me also clarify that I have 12 queries that are all performing this same function. What I essentially need to do is look at invoice number across the 12 queries and ensure get the unique count across all 12.

Commented:
Unless SQL has changed in 2007 don't you have to group by all non aggregate columns so your group by should be

Group By nv.InvoiceNumber, inv.VendorName, inv.DateSubmitted, inv.DateCheckRequested, inv.IM_Check_Confirmed_Date, inv.NetItemAmount, DateDiff("d",[DateSubmitted],[DateCheckRequested])

Author

Commented:
JArndt42 I need it to group by Invoice Number so that if there are two rows with the same invoice number they will be combined to one row with a sum total of NetItemAmount column. Thank you for your continued help.

Commented:
group by is from left to right. so your group by is saying I want to see all the invoices. and with those invoices I want to see the vendor. and with those vendors I want to see the date submitted. and........... you get the point. so if you want to see the total invoice numbers for a customer you would do something like this

Select inv.VendorName,count(inv.InvoiceNumber) as InvoiceCount
from
where
group by inv.VendorName

Author

Commented:
Hmmm... this did not work. I want to see all the invoices grouped up and consolidated (left most column). Do not need vendor name, if that makes it any easier. I net everything to be grouped by "invoice number" and by "netitemamount"

Commented:
so lets do something like this

Select inv.InvoiceNumber,count(InvoiceNumber) as InvCount,Sum(netitemamount) as TotalSum
from
where
group by inv.InvoiceNumber

I am assuming there are multiple like invoice numbers. this would group them, count how many per group, and sum the net amount for all the invoice numbers.

so if there were three invoice number and they were all 5 then the results would be
 
invoice 5 has three invoices with that number and the sum of the net amount is.....

Author

Commented:
This worked! I forgot to add one minor detail to this query. I have a datediff formula (I have a column DateRequested and DateSubmitted and I am finding the difference between the two). My first thing is I need to add this to my query (still grouped by invoice number) and then I am going to add to my where statement where the datediff is between 0 and 15. I also have the where statement so that the sum of the netitemamount is less than 100.

Please advise:
Select inv.InvoiceNumber,count(InvoiceNumber) as InvCount,Sum(NetItemAmount) as TotalSum
from tblInvoicesSubmitted as inv
where NetItemAmount <= 100
group by inv.InvoiceNumber;

Author

Commented:
Select inv.InvoiceNumber,count(InvoiceNumber) as InvCount,Sum(NetItemAmount) as TotalSum
from tblInvoicesSubmitted as inv
where (inv.TotalSum) <= 100
group by inv.InvoiceNumber;


Changed it to this, did not work says I need to enter a parameter value? Also, need the datediff.

Author

Commented:
SELECT inv.InvoiceNumber, count(InvoiceNumber) as InvCount, inv.VendorName, inv.DateSubmitted, inv.DateCheckRequested, inv.IM_Check_Confirmed_Date, sum(inv.NetItemAmount) as TotalSum, DateDiff("d",[DateSubmitted],[DateCheckRequested]) AS PaymentTime
FROM tblInvoicesSubmitted AS inv
WHERE NetItemAmount<=100 and DateDiff("d",[DateSubmitted],[DateCheckRequested]) BETWEEN 16 AND 30
ORDER BY DateDiff("d",[DateSubmitted],[DateCheckRequested]) DESC;

But it says 'InvoiceNumber' is not part of an aggregate function.

Author

Commented:
If that didn't make sense, is there anyway I can add a datediff to my original table. Then, with that I need a query to return all the values that fall between certain date ranges (from my date diff) column and different sums of netitemamount grouped by invoice number.

0-15 days, where sum (netitemamount) is between 0-100, 101-1000,1001+
15-30 days, where sum (netitemamount) is between 0-100, 101-1000,1001+
30-45 days, where sum (netitemamount) is between 0-100, 101-1000,1001+

Commented:
Good morning. in response to
SELECT inv.InvoiceNumber, count(InvoiceNumber) as InvCount, inv.VendorName, inv.DateSubmitted, inv.DateCheckRequested, inv.IM_Check_Confirmed_Date, sum(inv.NetItemAmount) as TotalSum, DateDiff("d",[DateSubmitted],[DateCheckRequested]) AS PaymentTime
FROM tblInvoicesSubmitted AS inv
WHERE NetItemAmount<=100 and DateDiff("d",[DateSubmitted],[DateCheckRequested]) BETWEEN 16 AND 30
ORDER BY DateDiff("d",[DateSubmitted],[DateCheckRequested]) DESC;

But it says 'InvoiceNumber' is not part of an aggregate function.

You need to group it still. after your where and before your order by

Author

Commented:
Please elaborate on the group it syntax GROUP BY what exact fields -- all of them?

Commented:
Ok In this case I believe that a Union query will work the best so this is what I have

SELECT InvoiceNumber,Count(InvoiceNumber) AS invcount, Sum(NetItemAmount) AS invsum
FROM tblInvoicesSubmitted AS TotalInv
WHERE DateDiff('d',[DateRequested],[datesubmitted]) Between 0 and 15
Group By InvoiceNumber
Union
SELECT InvoiceNumber,Count(InvoiceNumber) AS invcount, Sum(NetItemAmount) AS invsum
FROM tblInvoicesSubmitted AS TotalInv
WHERE DateDiff('d',[DateRequested],[datesubmitted]) Between 16 and 30
Group By InvoiceNumber
Union
SELECT InvoiceNumber,Count(InvoiceNumber) AS invcount, Sum(NetItemAmount) AS invsum
FROM tblInvoicesSubmitted AS TotalInv
WHERE DateDiff('d',[DateRequested],[datesubmitted]) Between 31 and 45
Group By InvoiceNumber

Commented:
I added a column named category so you know what the date difference is.

SELECT InvoiceNumber,Count(InvoiceNumber) AS invcount, Sum(NetItemAmount) AS invsum,"15" as Category
FROM tblInvoicesSubmitted AS TotalInv
WHERE DateDiff('d',[DateRequested],[datesubmitted]) Between 0 and 15
Group By InvoiceNumber
Union
SELECT InvoiceNumber,Count(InvoiceNumber) AS invcount, Sum(NetItemAmount) AS invsum,"30" as Cateogory
FROM tblInvoicesSubmitted AS TotalInv
WHERE DateDiff('d',[DateRequested],[datesubmitted]) Between 16 and 30
Group By InvoiceNumber
UNION
SELECT InvoiceNumber,Count(InvoiceNumber) AS invcount, Sum(NetItemAmount) AS invsum,"45" as Category
FROM tblInvoicesSubmitted AS TotalInv
WHERE DateDiff('d',[DateRequested],[datesubmitted]) Between 31 and 45
group by InvoiceNumber;

Commented:
I saved that query as qryUnion. based on that I made the following union query that takes care of your request for

0-15 days, where sum (netitemamount) is between 0-100, 101-1000,1001+
15-30 days, where sum (netitemamount) is between 0-100, 101-1000,1001+
30-45 days, where sum (netitemamount) is between 0-100, 101-1000,1001+

SELECT InvoiceNumber,Category,sum(invcount) as InvoiceCount, Sum(invsum) AS InvoiceSum,"<100" as Criteria
FROM qryUnion
WHERE invsum Between 0 And 100
GROUP BY InvoiceNumber,Category
Union
SELECT InvoiceNumber,Category,sum(invcount) as InvoiceCount, Sum(invsum) AS InvoiceSum,"Between 101 and 1000" as Criteria
FROM qryUnion
WHERE invsum Between 101 And 1000
GROUP BY InvoiceNumber,Category
Union
SELECT InvoiceNumber,Category,sum(invcount) as InvoiceCount, Sum(invsum) AS InvoiceSum,">1001" as Criteria
FROM qryUnion
WHERE invsum > 1001
GROUP BY InvoiceNumber,Category

Commented:
You can put anything you want in your results but remember that when using Union Queries is that the total number of columns in each union must be the same. In other words if you had a field to the first then you have to add it to the other unions as well.
Commented:
OK after playing with it for a little bit we can accomplish this in one query. Here you go. this is my final answer. well for now.

SELECT InvoiceNumber,Count(InvoiceNumber) AS invcount, Sum(NetItemAmount) AS invsum,"15 Days" as Category, IIf([invsum] Between 0 And 100,"<100",iif([invsum] Between 101 And 1000,"Between 100 and 1000",iif([invsum] > 1001,"Greater Than 1000","N/A"))) AS Criteria
FROM tblInvoicesSubmitted AS TotalInv
WHERE DateDiff('d',[DateRequested],[datesubmitted]) Between 0 and 15
Group By InvoiceNumber
Union
SELECT InvoiceNumber,Count(InvoiceNumber) AS invcount, Sum(NetItemAmount) AS invsum,"30 Days" as Cateogory, IIf([invsum] Between 0 And 100,"<100",iif([invsum] Between 101 And 1000,"Between 100 and 1000",iif([invsum] > 1001,"Greater Than 1000","N/A"))) AS Criteria
FROM tblInvoicesSubmitted AS TotalInv
WHERE DateDiff('d',[DateRequested],[datesubmitted]) Between 16 and 30
Group By InvoiceNumber
UNION SELECT InvoiceNumber,Count(InvoiceNumber) AS invcount, Sum(NetItemAmount) AS invsum,"45 Days" as Category, IIf([invsum] Between 0 And 100,"<100",iif([invsum] Between 101 And 1000,"Between 100 and 1000",iif([invsum] > 1001,"Greater Than 1000","N/A"))) AS Criteria
FROM tblInvoicesSubmitted AS TotalInv
WHERE DateDiff('d',[DateRequested],[datesubmitted]) Between 31 and 45
group by InvoiceNumber;

Author

Commented:
I've requested that this question be deleted for the following reason:

I received the answer I needed.

Commented:
did anything I give you help at all? what was your final solution?

Author

Commented:
I've requested that this question be deleted for the following reason:

This SQL cannot be published on the web, please delete this question.

Commented:
should I get nothing for taking the time to attempt to answer your question and spending my time constructing the SQL I did?