Solved

SQL (Group by)

Posted on 2014-12-16
5
87 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:
ScottPletcher 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

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.

Join & Write a Comment

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now