Solved

SQL (Group by)

Posted on 2014-12-16
5
92 Views
Last Modified: 2014-12-17
Hi,

I have a select statement that I just want to group by

EditTime and AppID

instead of grouping by

      EditTime
    , AppID
    , AppName
    , MajorUpgradeCost
    , PlannedUpgradeYear
    , UpgradeStrategy
    , SoftwareVersion
    , OldestVersionSupportedBySupplier
    , EmployeeName

but I do want to select all the above field but I just want to group by the "EditTime" and "AppID"
How can I do this?

SELECT
      EditTime
    , AppID
    , AppName
    , MajorUpgradeCost
    , PlannedUpgradeYear
    , UpgradeStrategy
    , SoftwareVersion
    , OldestVersionSupportedBySupplier
    , EmployeeName
FROM EDIT_ApplicationDescription
WHERE ((datalength(SoftwareVersion)!=0) and ((SoftwareVersion != '')  or (SoftwareVersion IS NOT NULL) or (SoftwareVersion <> '')))
or  
 ((datalength(MajorUpgradeCost)!=0) and ((MajorUpgradeCost != '')  or (MajorUpgradeCost IS NOT NULL) or (MajorUpgradeCost <> '')))
or 
((datalength(PlannedUpgradeYear)!=0) and ((PlannedUpgradeYear != '')  or (PlannedUpgradeYear IS NOT NULL) or (PlannedUpgradeYear <> '')))
or
((datalength(UpgradeStrategy)!=0) and ((UpgradeStrategy != '')  or (UpgradeStrategy IS NOT NULL) or (UpgradeStrategy <> '')))
or 
((datalength(OldestVersionSupportedBySupplier)!=0) and ((OldestVersionSupportedBySupplier != '')  or (OldestVersionSupportedBySupplier IS NOT NULL) 
or (OldestVersionSupportedBySupplier <> '')))
group by AppID, EditTime, AppName
    , MajorUpgradeCost
    , PlannedUpgradeYear
    , UpgradeStrategy
    , SoftwareVersion
    , OldestVersionSupportedBySupplier
    , EmployeeName
order by EditTime desc

Open in new window

0
Comment
Question by:lulu50
  • 3
5 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40502635
>but I do want to select all the above field but I just want to group by the "EditTime" and "AppID"
Just a thought, I don't see any aggregate functions like SUM(), MIN(), MAX(), COUNT(), etc., so why are we grouping in the first place?

If you only want to return unique rows, change SELECT to SELECT DISTINCT.

If there's something else you need, spell it out in this question, specifically 'I want to do {x} with {all columns other than {"EditTime" and "AppID"}'
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 40503012
If you want to list all the details, you don't need a GROUP BY, just ORDER BY.  If you want to consolidate some rows, let me know how and we'll work on that.

Also, you can greatly simplify the exceedingly complex WHERE conditions, as below.

SELECT
      EditTime
    , AppID
    , AppName
    , MajorUpgradeCost
    , PlannedUpgradeYear
    , UpgradeStrategy
    , SoftwareVersion
    , OldestVersionSupportedBySupplier
    , EmployeeName
FROM EDIT_ApplicationDescription
WHERE SoftwareVersion > '' OR
      MajorUpgradeCost > '' OR
      PlannedUpgradeYear > '' OR
      UpgradeStrategy > '' OR
      OldestVersionSupportedBySupplier > ''
ORDER BY
      EditTime DESC
    , AppID
0
 

Author Comment

by:lulu50
ID: 40504423
Thank you Scott!!!
0
 

Author Comment

by:lulu50
ID: 40504487
I've requested that this question be closed as follows:

Accepted answer: 0 points for lulu50's comment #a40504423

for the following reason:

Thanks
0
 

Author Closing Comment

by:lulu50
ID: 40504488
Thank you
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Error - Query 6 42
Loop through SQL parameters and insert to temp table? 4 46
SQL trigger 5 23
Setting variables in a stored procedure 5 21
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

830 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