Distinct and count

Question

Distinct and count

SQLAssAS

SSCertifiable

Points: 7246
More actions
July 14, 2014 at 3:42 am

#397052

SELECT Distinct MAGINUS_SUPPLIER.SUPPLIER_ACCOUNT, MAGINUS_SUPPLIER.SUPPLIER_NAME,MAGINUS_SUPPLIER.ADDRESS1, MAGINUS_SUPPLIER.ADDRESS2, MAGINUS_SUPPLIER.ADDRESS3, MAGINUS_SUPPLIER.ADDRESS4, MAGINUS_SUPPLIER.ADDRESS5, MAGINUS_SUPPLIER.ADDRESS6, MAGINUS_SUPPLIER.POSTCODE, MAGINUS_SUPPLIER.PAYMENT_TERM, MAGINUS_BANK_DETAILS.BANK_SORT_CODE, MAGINUS_BANK_DETAILS.BANK_ACCOUNT_NUM, MAGINUS_BANK_DETAILS.BANK_REFERENCE, xxxCustDelivDump.InvDate
FROM (MAGINUS_SUPPLIER INNER JOIN MAGINUS_BANK_DETAILS ON MAGINUS_SUPPLIER.SUPPLIER_ACCOUNT = MAGINUS_BANK_DETAILS.ACCOUNT_CODE) INNER JOIN xxxCustDelivDump ON MAGINUS_BANK_DETAILS.ACCOUNT_CODE = xxxCustDelivDump.INVOICE_ACCOUNT
WHERE (((xxxCustDelivDump.InvDate)> '7/14/2013'))
I have this query which has been provided to me, from an access query converter.
It is currently returning all results as it should. I need to change this so that it only each MAGINUS_SUPPLIER.SUPPLIER_ACCOUNT only shows once and ideally, a count is put on to show how many times it actually occurs.
Any help would be appreciated

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

What do you want to have happen to the rest of the columns? Max? Min? Something else?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 2

The aim of this is to show which suppliers (along with the other fields selects) have been used in the last year.

So it is basically all supplier information which should be the same for each supplier name entry. So i don't think we have to do anything with it.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

You want the number of count, as grouped by the entire of the select clause?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 4

I need to count the number of times the supplier name is used but only display each supplier name once.

I am not sure what else we have to do with the other columns? each supplier name has consistent data across every entry for that supplier name. So if we set them to Min.. that should do the trick?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Does that include xxxCustDelivDump.InvDate?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 6

SQLAssAS

SSCertifiable

Points: 7246

July 14, 2014 at 5:08 am

#1729101

Yes please,

Cheers for your help with this.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Here's your query with table aliases applied for readability:

SELECT DISTINCT

ms.SUPPLIER_ACCOUNT,

ms.SUPPLIER_NAME,

ms.ADDRESS1,

ms.ADDRESS2,

ms.ADDRESS3,

ms.ADDRESS4,

ms.ADDRESS5,

ms.ADDRESS6,

ms.POSTCODE,

ms.PAYMENT_TERM,

mb.BANK_SORT_CODE,

mb.BANK_ACCOUNT_NUM,

mb.BANK_REFERENCE,

cd.InvDate

FROM MAGINUS_SUPPLIER ms

INNER JOIN MAGINUS_BANK_DETAILS mb

ON ms.SUPPLIER_ACCOUNT = mb.ACCOUNT_CODE

INNER JOIN xxxCustDelivDump cd

ON mb.ACCOUNT_CODE = cd.INVOICE_ACCOUNT

WHERE cd.InvDate > '7/14/2013'

You could help by providing a couple of sample data sets. Firstly, a few rows output from this query as it is now. Secondly, how you would want those rows to appear in your output. Can you do this?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

SQLAssAS SSCertifiable Points: 7246 More actions · Answer 8

Thanks -

2 excels attached. As is is how it stands now (obviously sensitive data removed)

We just need each supplier code to have 1 entry, not multiple.

All of the information per each supplier account entry is the same, apart from the invDate obviously. We just need to see which suppliers we have used in the last 12 months not every single time it has been used.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

SQLSteve (7/14/2014)
Thanks -
2 excels attached. As is is how it stands now (obviously sensitive data removed)
We just need each supplier code to have 1 entry, not multiple.
All of the information per each supplier account entry is the same, apart from the invDate obviously. We just need to see which suppliers we have used in the last 12 months not every single time it has been used.

If I wanted to tackle this project I'd first have to create the tables (guessing the datatypes) then convert your Excel data into a series of INSERTS, and finally load the tables up. It's all trivial to do but does take up time, and often takes more time than solving the problem. There's an article which shows you how to do this, there's a link to it in my sig; "please read this". The upside is you will get more folks willing to tackle this problem, and you will also get tested code - tested against whatever data you provide.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

pietlinden SSC Guru Points: 63428 More actions · Answer 10

I need to count the number of times the supplier name is used but only display each supplier name once.

If you imported this from Access, you probably have some restructuring to do. You could do something like this (thinking in steps)..

1. create the DISTINCT query to return the list of Suppliers without the date at the end. (Or use MAX() on the date field to get the time it was last used and group by the rest).

2. do a count against the DISTINCT (which would become a subquery).

bens4lsu SSC Journeyman Points: 76 More actions · Answer 11

Isn't just as simple as adding a count() and a group by?

SELECT MAGINUS_SUPPLIER.SUPPLIER_ACCOUNT

, MAGINUS_SUPPLIER.SUPPLIER_NAME,MAGINUS_SUPPLIER.ADDRESS1

, MAGINUS_SUPPLIER.ADDRESS2

, MAGINUS_SUPPLIER.ADDRESS3

, MAGINUS_SUPPLIER.ADDRESS4

, MAGINUS_SUPPLIER.ADDRESS5

, MAGINUS_SUPPLIER.ADDRESS6

, MAGINUS_SUPPLIER.POSTCODE

, MAGINUS_SUPPLIER.PAYMENT_TERM

, MAGINUS_BANK_DETAILS.BANK_SORT_CODE

, MAGINUS_BANK_DETAILS.BANK_ACCOUNT_NUM

, MAGINUS_BANK_DETAILS.BANK_REFERENCE

, xxxCustDelivDump.InvDate

, COUNT(*)

FROM MAGINUS_SUPPLIER

INNER JOIN MAGINUS_BANK_DETAILS ON MAGINUS_SUPPLIER.SUPPLIER_ACCOUNT = MAGINUS_BANK_DETAILS.ACCOUNT_CODE

INNER JOIN xxxCustDelivDump ON MAGINUS_BANK_DETAILS.ACCOUNT_CODE = xxxCustDelivDump.INVOICE_ACCOUNT

WHERE xxxCustDelivDump.InvDate)> '7/14/2013'

GROUP BY MAGINUS_SUPPLIER.SUPPLIER_ACCOUNT

, MAGINUS_SUPPLIER.SUPPLIER_NAME,MAGINUS_SUPPLIER.ADDRESS1

, MAGINUS_SUPPLIER.ADDRESS2

, MAGINUS_SUPPLIER.ADDRESS3

, MAGINUS_SUPPLIER.ADDRESS4

, MAGINUS_SUPPLIER.ADDRESS5

, MAGINUS_SUPPLIER.ADDRESS6

, MAGINUS_SUPPLIER.POSTCODE

, MAGINUS_SUPPLIER.PAYMENT_TERM

, MAGINUS_BANK_DETAILS.BANK_SORT_CODE

, MAGINUS_BANK_DETAILS.BANK_ACCOUNT_NUM

, MAGINUS_BANK_DETAILS.BANK_REFERENCE

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

What InvoiceDate do you want displayed? Looking at a supplier with multiple entries there are different Invoice Dates.