Using DISTINCT in my query.

Hi

I have this slight problem with my license database.

I am trying to use DISTINCT in my query, and it's doing what it's suppose to do.

But in my running columns, I have a query called SoftwareCount with counts the number of software licenses I have and puts a total at the top.

Now if there are two software titles, it will show only one, which is what I want.

This is what it looks like when DEPT is queried.

Software Title: Dreamweaver              Licensce Type: Paper             Number of Licenses:5
Software Title: Dreamweaver              Licensce Type: Paper             Number of Licenses:5

Total Software License for Dept:10


See they look the same which if fine, but when I use the key word DISTICNT in the SELECT coulmn, it looks like this

Software Title: Dreamweaver              Licensce Type: Paper             Number of Licenses:5

Total Software License for Dept:10


Which is fine, BUT I want the place where it says: Number of licenses to say 10.  I am just trying to avoid it from repeating software titles with the same license type and number of licenses, but would like to keep a count of the total when it eleminates repeating data.

Here is one of the queries I am using.
<cfquery name="SoftwareList" datasource="softtrack">
SELECT DISTINCT tblJunction.DeptID, tblSoft.Title, tblLicense.LicenseType, tblJunction.HowMany
FROM (tblJunction INNER JOIN tblSoft ON tblJunction.SoftID=tblSoft.SoftID) INNER JOIN tblLicense ON tblJunction.LicenseID=tblLicense.LicenseID
WHERE DeptID=<cfqueryparam cfsqltype="CF_SQL_INTEGER"
       value="#url.DeptID#">
</cfquery>

Does anyone need to see what database layout I have?  I can post this much if you like.
LVL 1
fixx17Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MauseCommented:
Hi there,

What about sum and group by like this:

<cfquery name="SoftwareList" datasource="softtrack">
SELECT tblJunction.DeptID, tblSoft.Title, tblLicense.LicenseType, sum(tblJunction.HowMany) as HowMany_total
FROM (tblJunction INNER JOIN tblSoft ON tblJunction.SoftID=tblSoft.SoftID) INNER JOIN tblLicense ON tblJunction.LicenseID=tblLicense.LicenseID
WHERE DeptID=<cfqueryparam cfsqltype="CF_SQL_INTEGER"
      value="#url.DeptID#">
group by tblJunction.DeptID
</cfquery>

Mause

PE_CF_DEVCommented:
One small correction in the group by:
<cfquery name="SoftwareList" datasource="softtrack">
SELECT tblJunction.DeptID, tblSoft.Title, tblLicense.LicenseType, sum(tblJunction.HowMany) as HowMany_total
FROM (tblJunction INNER JOIN tblSoft ON tblJunction.SoftID=tblSoft.SoftID) INNER JOIN tblLicense ON tblJunction.LicenseID=tblLicense.LicenseID
WHERE DeptID=<cfqueryparam cfsqltype="CF_SQL_INTEGER"
      value="#url.DeptID#">
group by tblJunction.DeptID, tblSoft.Title, tblLicense.LicenseType
</cfquery>


You need to put all the collumns in the group by clause.
fixx17Author Commented:
I am going home for the day, I will try this when I get back to work tomorrow.

Thanks,

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

fixx17Author Commented:
Hi guys, sorry I took so long,

This is what I put in.

<cfquery name="SoftwareList" datasource="softtrack">
SELECT tblJunction.DeptID, tblSoft.Title, tblLicense.LicenseType, tblJunction.HowMany
FROM (tblJunction INNER JOIN tblSoft ON tblJunction.SoftID=tblSoft.SoftID) INNER JOIN tblLicense ON tblJunction.LicenseID=tblLicense.LicenseID
WHERE DeptID=<cfqueryparam cfsqltype="CF_SQL_INTEGER"
       value="#url.DeptID#">
group by tblJunction.DeptID, tblSoft.Title, tblLicense.LicenseType


This is the error I got.

ODBC Error Code = 37000 (Syntax error or access violation)


[Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'HowMany' as part of an aggregate function.


SQL = "SELECT tblJunction.DeptID, tblSoft.Title, tblLicense.LicenseType, tblJunction.HowMany FROM (tblJunction INNER JOIN tblSoft ON tblJunction.SoftID=tblSoft.SoftID) INNER JOIN tblLicense ON tblJunction.LicenseID=tblLicense.LicenseID WHERE DeptID=? group by tblJunction.DeptID, tblSoft.Title, tblLicense.LicenseType"

Query Parameter Value(s) -

Parameter #1 = 5

Data Source = "SOFTTRACK



I read more about Group BY and confident about writing it this way, but trying to understand why I get this error.


PE_CF_DEVCommented:
sum(tblJunction.HowMany) as HowMany_total instead of tblJunction.HowMany

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fixx17Author Commented:
WOW, that was great.  Sure beats the DISTINCT clause.

The only thing I changed was this


sum(tblJunction.HowMany) as HowMany_total

to this

sum(tblJunction.HowMany) as HowMany


Thanks again
MauseCommented:

NP!!

Mause
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.