?
Solved

SQL (Group by)

Posted on 2014-12-16
5
Medium Priority
?
103 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 66

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 2000 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

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

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.
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

770 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