Solved

Using DISTINCT in my query.

Posted on 2003-11-20
7
316 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
JVM encoding. How to change encoding. 27 120
DNS, website, godaddy 6 94
How do disable only TLSv1.0 in Oracle Sun One 7.1 Server 9 128
what is socket pooling? 8 14
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…

735 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