Solved

Using DISTINCT in my query.

Posted on 2003-11-20
7
317 Views
Last Modified: 2013-12-24
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
Comment
Question by:fixx17
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 10

Expert Comment

by:Mause
ID: 9790193
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
 
LVL 6

Expert Comment

by:PE_CF_DEV
ID: 9790435
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
 
LVL 1

Author Comment

by:fixx17
ID: 9790519
I am going home for the day, I will try this when I get back to work tomorrow.

Thanks,

0
Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

 
LVL 1

Author Comment

by:fixx17
ID: 9799310
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
 
LVL 6

Accepted Solution

by:
PE_CF_DEV earned 400 total points
ID: 9799323
sum(tblJunction.HowMany) as HowMany_total instead of tblJunction.HowMany
0
 
LVL 1

Author Comment

by:fixx17
ID: 9799670
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
 
LVL 10

Expert Comment

by:Mause
ID: 9799980

NP!!

Mause
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
What You Need to Know when Searching for a Webhost Provider
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question