Count distinct (1 Viewer)

[Les Isaacs]

Hi All
I thought I knew how to use Count/Select distinct, but apparently not!
I have this:

SELECT DISTINCT Count([Order #]) AS [CountOfOrder #], Sum([qty]*[Item's Price]) AS [Value], OrderDate
FROM tbl_orders
GROUP BY OrderDate;

The query runs, but the value shown in the 1st column [CountOfOrder #] isn't right - for each OrderDate it's giving a count of all the records for each [order #], rather than a count of each unique [order #] value, which is what I want.
I'm sure it's obvious, but not to me!
Hope someone can help.

 

[isladogs]

First, you need to fix your naming convention or you will have MAJOR problems in the future

SELECT DISTINCT Count([Order #]) AS [CountOfOrder #], Sum([qty]*[Item's Price]) AS [Value], OrderDate
FROM tbl_orders
GROUP BY OrderDate;

Don't use spaces, apostrophes or other characters such %, # in field names.
Also avoid reserved words such as Value, Name

This is better though it doesn't solve your question:

SELECT DISTINCT Count([OrderNo]) AS CountOfOrderNo, Sum([qty]*[ItemPrice]), OrderDate
FROM tblOrders
GROUP BY OrderDate;

To get what you want, change this further to:

SELECT DISTINCT OrderNo, Count([OrderNo]) AS CountOfOrderNo
FROM tblOrders
GROUP BY OrderNo;

 

[Les Isaacs]

Hi Colin
Thanks for your reply.
I am aware of the horrendous field names - but it's not my fault, I inherited them, and am working on amending them - but there are other systems involved so it's not trivial!
So the code I now have is

SELECT DISTINCT tbl_Orders.[Order #], Count(tbl_Orders.[Order #]) AS CountOfOrderNo, Sum([qty]*[Item's Price]) AS [Value]
FROM tbl_Orders
GROUP BY tbl_Orders.[Order #];

... but this doesn't work - it returns a record for each [order #] value, with the number of record for each [order #] in the 2nd column (and the value of those records in the 3rd column). I've left the Group By [Orderdate] out for now, but ultimately I just need to see, for each [OrderDate], the number of unique [order #] values, and the total 'value' of all the records for that date.
Thanks again.
Les

 

[isladogs]

Hi Colin
Thanks for your reply.
I am aware of the horrendous field names - but it's not my fault, I inherited them, and am working on amending them - but there are other systems involved so it's not trivial!
So the code I now have is

SELECT DISTINCT tbl_Orders.[Order #], Count(tbl_Orders.[Order #]) AS CountOfOrderNo, Sum([qty]*[Item's Price]) AS [Value]
FROM tbl_Orders
GROUP BY tbl_Orders.[Order #];

... but this doesn't work - it returns a record for each [order #] value, with the number of record for each [order #] in the 2nd column (and the value of those records in the 3rd column). I've left the Group By [Orderdate] out for now, but ultimately I just need to see, for each [OrderDate], the number of unique [order #] values, and the total 'value' of all the records for that date.
Thanks again.
Les

That's not quite what you asked for originally.
If you think about what you're asking Access to do here, it should be obvious why its not doing what you ask.

To group with a count of one field and a sum of another you need to use the table twice - once to get total orders for the day & separately to get the total value. You then combine the two by joining the fields [Order #] & OrderDate

This should do it:

SELECT tbl_Orders.[Order #], tbl_Orders.PupilID, Count(tbl_Orders.[Order #]) AS TotalOrders, Sum([qty]*[Item's Price]) AS TotalValue
FROM tbl_Orders INNER JOIN tbl_Orders AS tbl_Orders_1 ON (tbl_Orders.[Order #] = tbl_Orders_1.[Order #]) AND (tbl_Orders.OrderDate = tbl_Orders_1.OrderDate)
GROUP BY tbl_Orders.[Order #], tbl_Orders.OrderDate;

Note that DISTINCT is probably now redundant & I've left it out
Try with & without & compare the results.

If the query doesn't compile, its probably a typo due to 'your' field names

I really wouldn't put off the renaming process.
It will get harder the longer you wait

 

[Les Isaacs]

Hi again Colin

I don't know if it's me, but I'm now getting a separate record returned by the query for each unique value of [order #]. The code I now have is:

SELECT tbl_Orders.[Order #], Count(tbl_Orders.[Order #]) AS TotalOrders, Sum(tbl_Orders.[qty]*tbl_Orders.[Item's Price]) AS TotalValue, tbl_Orders.label_generated
FROM tbl_Orders INNER JOIN tbl_Orders AS tbl_Orders_1 ON (tbl_Orders.[Order #] = tbl_Orders_1.[Order #]) AND (tbl_Orders.label_generated = tbl_Orders_1.label_generated)
GROUP BY tbl_Orders.[Order #], tbl_Orders.label_generated;

(you will see that I am now using [label_generated] in place of [OrderDate] - both are date fields).

I want the query to return just one record for each [label_generated] date.

I'm sure I'm making a meal of this, but cannot fathom it!
Thanks for any further help.
Les

 

[isladogs]

I assume you still mean one record per order number for each date (as you said before)

Does it work correctly using OrderDate as you had originally?
Have you tried adding DISTINCT again?

Otherwise, suggest you upload the relevant parts of your db so it can be checked with your data

 

[Les Isaacs]

No, I don't want one record per order number for each date (don't think I said this before?). What I want is "for each OrderDate ..... a count of each unique [order #] value, " (which I did say in my original post!). I've confused myself a bit with this though, so not surprising if I've confused you

 

[plog]

I suggest you provide sample data to demonstrate what is going into the query and what you expect out of the query. Provide 2 sets of data:

A. Starting sample data from tables. Provide table and field names and enough data to cover all cases.

B. Expected results of A. Show exactly what data should be returned based on using the data in A.

 

[Les Isaacs]

OK, here's the sample data in [tbl_orders]. The expected results should look like the output from qry_OrdersAnalysis3 - in that the query should return 7 rows - BUT the value of [CountOfOrderNo] in the first record (where [packingDate = 05/04/2017) should be 107 (as there are 107 unique values of [order #] in tbl_orders for that date, as shown in the query 'qry_ordersOn5April2017'), not 455 (which is the total number of records in tbl_orders for that date).

Hope that explains exactly what I'm trying to do!
Thanks for any help.
Les

 

[plog]

Hope that explains exactly what I'm trying to do!

Nope, don't want an explanation. Want an example.

Show me what you exactly want the results to be. Don't get me close and then explain it, show me the exact data you want for every record.

 

[isladogs]

I started this response before Plog's post & didn't get round to uploading until now

OK - I'm not going to try & clarify what exactly you meant your query to show.

However, having checked your table, there ARE 455 records where the date field starts with 05/04/2017.
So this means qryOrdersAnalysis3 gives the CORRECT value for total orders & as far as I can see gives what I believe you want from this whole exercise

Your query qry_ordersOn5April2017 is pointless as you are grouping just one field
Remove the grouping & you get 455 records
If you change your qry_ordersOn5April2017 to count the orders on 05/04/2017 you also get 455.

Modified version of your database attached with these additional queries
I've changed the 'Item's Price' field to currency but otherwise made no changes to your table structure or data

It would have been easier to follow what you are doing if you were consistent with field names or aliases e.g. PackingDate / label_generated

Also its a pity you didn't CHECK your data rather than believe your wronly structured query

 

[Les Isaacs]

Plog
I'm not sure how I could show you an example of the output I want from the query - unless I create a table that looks like my desired query results? But I though I had explained (sorry for using that word again!) exactly what I wanted by saying

value of [CountOfOrderNo] in the first record (where [packingDate = 05/04/2017) should be 107 (as there are 107 unique values of [order #] in tbl_orders for that date, as shown in the query 'qry_ordersOn5April2017'), not 455 (which is the total number of records in tbl_orders for that date).

So do I need to create a table that looks like the output from qry_OrdersAnalysis3 (ie same number of records returned, and same column headings) EXCEPT that the value of [CountOfOrderNo] in the first record (where [packingDate = 05/04/2017) is 107?

Ridders

So this means qryOrdersAnalysis3 gives the CORRECT value for total orders & as far as I can see gives what I believe you want from this whole exercise

- no: I appreciate that it gives the correct value for the query as it is written, but the point of this post is that it must be written wrongly - because I want to see the number of UNIQUE values of [order #] for each date (originally OrderDate, now label_generated date - but surely these are interchangeable and are not significant to the problem of how to get the counts I want).

Your query qry_ordersOn5April2017 is pointless as you are grouping just one field

- the point of that query was simply to show where the required value of 107 comes from!

I hope this doesn't come across as if I'm being awkward or ungrateful for the help that you guys are trying to provide - I am definitely grateful: just frustrated that I can't seem to give you the info you need in a way that you can use it.

Thanks again
Les

 

[plog]

I'm not sure how I could show you an example of the output I want from the query - unless I create a table that looks like my desired query results?

Yes. Or put both sets of my requested data into Excel, one on each tab.

 

[Les Isaacs]

Okey dokey, here it is.

 

[isladogs]

Hi Les - got your name right this time ...

Now that's what you should have done earlier!

See attached for a solution based on existing query qryOrdersAnalysis3 & qryOrdersPackingDate. You could easily tidy this up but it works

As I said previously it needed 2 queries combined to get the result you want.

You didn't come across as rude (you should read some of the other posts from recently). However, we were all frustrated by being unable to understand what you wanted so were groping in the dark

In fact I apologise for being a bit stroppy in my last post.
It was only when Amazon responded to my last online order that I realised why each order could have more than one record with different packing dates. Doh!

 

[plog]

First, you really need to name your fields better. Only use alpha-numeric characters and underscores. Special characters and spaces make it harder to code.

Second, you to achieve the result you want you will need a subquery:

SELECT DateValue([label_generated]) AS PackingDate, tbl_orders.[Order #] AS UniqueOrder, Sum([Qty]*[Item's Price]) AS SubValue
FROM tbl_orders
GROUP BY DateValue([label_generated]), tbl_orders.[Order #];

Paste that into a new query object and name it '_sub1'. It groups your data by unique order. Then use this query to get the data you want:

SELECT [_sub1].PackingDate, Count([_sub1].UniqueOrder) AS UniqueOrders, Sum([_sub1].SubValue) AS [Value], Sum([SubValue])/Count([UniqueOrder]) AS AverageValue
FROM _sub1
GROUP BY [_sub1].PackingDate;

 

[Les Isaacs]

Morning Colin (ridders?)
Thanks ever so much for this - it obviously works, and - more importantly - I can see how! Glad I wasn't coming across as rude, and I didn't think you were stroppy: so we all happy!
Thanks again
Les

 

[isladogs]

You're welcome