Solved

Using DISTINCT in my query.

Posted on 2003-11-20
7
315 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
  • 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
Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

831 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