[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 322
  • Last Modified:

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.
0
fixx17
Asked:
fixx17
  • 3
  • 2
  • 2
1 Solution
 
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

0
 
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.
0
 
fixx17Author Commented:
I am going home for the day, I will try this when I get back to work tomorrow.

Thanks,

0
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
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.


0
 
PE_CF_DEVCommented:
sum(tblJunction.HowMany) as HowMany_total instead of tblJunction.HowMany
0
 
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
0
 
MauseCommented:

NP!!

Mause
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now