Solved

SQL (Group by)

Posted on 2014-12-16
5
93 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
[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
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

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