Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

"Where, Group By and Order By" help

Status
Not open for further replies.
jmaclaurin

jmaclaurin

IS-IT--Management
May 10, 2005
6
CA
I would like the query to output exactly as it is written and not sort, but from what I can find, SQL requires a group by or order by. When I remove them, I get errors. How can I do this?


Code: 
SELECT TOP 100 PERCENT COUNT(MachineID) AS Total, DisplayName00 AS Application  
FROM   Add_or_Remove_Program_DATA  
WHERE   (DisplayName00 = 'Adobe Reader 7.0') OR
        (DisplayName00 = 'Microsoft Data Access Components KB870669') OR
        (DisplayName00 = 'Microsoft Office Outlook 2003') OR
        (DisplayName00 = 'Windows 2000 Hotfix - KB824146') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB824146') OR  
        (DisplayName00 = 'Windows 2000 Hotfix - KB828035') OR    
        (DisplayName00 = 'Windows XP Hotfix - KB828035') OR      
        (DisplayName00 = 'Windows 2000 Hotfix - KB828028') OR          
        (DisplayName00 = 'Windows XP Hotfix - KB828028') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB835732') OR
        (DisplayName00 = 'Windows XP Hotfix - KB835732') OR
        (DisplayName00 = 'Windows 2000 Hotfix - KB828741') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB828741') OR
        (DisplayName00 = 'Outlook Express Q837009') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB837001') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB837001') OR  
        (DisplayName00 = 'Windows 2000 Hotfix - KB841873') OR  
        (DisplayName00 = 'Windows XP Hotfix - KB841873') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB840315') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB840315') OR 
        (DisplayName00 = 'Microsoft VGX Q833989') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB833987') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB824151') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB824151') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB841533') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB841533') OR
        (DisplayName00 = 'Windows 2000 Hotfix - KB840987') OR
        (DisplayName00 = 'Windows XP Hotfix - KB840987') OR
        (DisplayName00 = 'Windows XP Hotfix - KB873376') OR  
        (DisplayName00 = 'Windows 2000 Hotfix - KB841356') OR  
        (DisplayName00 = 'Windows XP Hotfix - KB841356') OR   
        (DisplayName00 = 'Windows 2000 Hotfix - KB890175') OR  
        (DisplayName00 = 'Windows XP Hotfix - KB890175') OR  
        (DisplayName00 = 'Windows 2000 Hotfix - KB891711') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB891711') OR  
        (DisplayName00 = 'Windows Media Player 9 Hotfix [See KB885492 for more information]') OR
        (DisplayName00 = 'Windows 2000 Hotfix - KB885250') OR
        (DisplayName00 = 'Windows XP Hotfix - KB885250') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB873333') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB873333') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB891781') OR  
        (DisplayName00 = 'Windows XP Hotfix - KB891781') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB867282') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB867282') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB888113') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB888113') OR
        (DisplayName00 = 'Windows 2000 Hotfix - KB893086') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB893086') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB892944') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB892944') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB890859') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB890859') OR
        (DisplayName00 = 'Windows 2000 Hotfix - KB893066') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB893066') OR
        (DisplayName00 = 'Windows 2000 Hotfix - KB890923') OR
        (DisplayName00 = 'Windows XP Hotfix - KB890923') OR
        (DisplayName00 = 'Windows XP Hotfix - KB833987') OR
        (DisplayName00 = 'Windows 2000 Service Pack 4')
  GROUP BY DisplayName00
  ORDER BY DisplayName00
 
Sort by date Sort by votes
you need a group by as you are doing a count. You don't need the order by as far as I can see. What do you want the output to look like?
 
Upvote 0 Downvote
The aggregate function COUNT() does indeed require a GROUP BY clause.

The only exception would be when it is the only element of the SELECT clause, when you just need a count of rows. Otherwise the aggregate functions, COUNT(), SUM(), MAX(), and MIN() are used to get breakdowns by something. You must mention that something in the GROUP BY clause. And it only makes sense to mention it in the SELECT clause as well; other wise you just get a list of numbers with no idea of what category they belong to.

The ORDER BY clause is optional. But usually sorting the categories in the breakdown is useful.

Doesnt TOP 100 PERCENT mean all of them? Get rid of those keywords.

In the WHERE clause you have a lot of items. Have you possibly listed every item? If not, do you have some column with a value or a few values which marks exactly the items listed? If so that would be the column to use a a condition in the WHERE clause.
 
Upvote 0 Downvote
I removed the TOP 100 PERCENT and ORDER BY DisplayName00 and still the same results. I am trying to group the results together so that I can report on the total number of machines that have the patch installed.
 
Upvote 0 Downvote
If you just want the total number:
Code: 
SELECT COUNT(MachineID)
FROM   Add_or_Remove_Program_DATA  
WHERE   (DisplayName00 = 'Adobe Reader 7.0') OR
        (DisplayName00 = 'Microsoft Data Access Components KB870669') OR
        (DisplayName00 = 'Microsoft Office Outlook 2003') OR
        (DisplayName00 = 'Windows 2000 Hotfix - KB824146') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB824146') OR  
        (DisplayName00 = 'Windows 2000 Hotfix - KB828035') OR    
        (DisplayName00 = 'Windows XP Hotfix - KB828035') OR      
        (DisplayName00 = 'Windows 2000 Hotfix - KB828028') OR          
        (DisplayName00 = 'Windows XP Hotfix - KB828028') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB835732') OR
        (DisplayName00 = 'Windows XP Hotfix - KB835732') OR
        (DisplayName00 = 'Windows 2000 Hotfix - KB828741') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB828741') OR
        (DisplayName00 = 'Outlook Express Q837009') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB837001') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB837001') OR  
        (DisplayName00 = 'Windows 2000 Hotfix - KB841873') OR  
        (DisplayName00 = 'Windows XP Hotfix - KB841873') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB840315') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB840315') OR 
        (DisplayName00 = 'Microsoft VGX Q833989') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB833987') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB824151') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB824151') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB841533') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB841533') OR
        (DisplayName00 = 'Windows 2000 Hotfix - KB840987') OR
        (DisplayName00 = 'Windows XP Hotfix - KB840987') OR
        (DisplayName00 = 'Windows XP Hotfix - KB873376') OR  
        (DisplayName00 = 'Windows 2000 Hotfix - KB841356') OR  
        (DisplayName00 = 'Windows XP Hotfix - KB841356') OR   
        (DisplayName00 = 'Windows 2000 Hotfix - KB890175') OR  
        (DisplayName00 = 'Windows XP Hotfix - KB890175') OR  
        (DisplayName00 = 'Windows 2000 Hotfix - KB891711') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB891711') OR  
        (DisplayName00 = 'Windows Media Player 9 Hotfix [See KB885492 for more information]') OR
        (DisplayName00 = 'Windows 2000 Hotfix - KB885250') OR
        (DisplayName00 = 'Windows XP Hotfix - KB885250') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB873333') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB873333') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB891781') OR  
        (DisplayName00 = 'Windows XP Hotfix - KB891781') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB867282') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB867282') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB888113') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB888113') OR
        (DisplayName00 = 'Windows 2000 Hotfix - KB893086') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB893086') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB892944') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB892944') OR 
        (DisplayName00 = 'Windows 2000 Hotfix - KB890859') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB890859') OR
        (DisplayName00 = 'Windows 2000 Hotfix - KB893066') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB893066') OR
        (DisplayName00 = 'Windows 2000 Hotfix - KB890923') OR
        (DisplayName00 = 'Windows XP Hotfix - KB890923') OR
        (DisplayName00 = 'Windows XP Hotfix - KB833987') OR
        (DisplayName00 = 'Windows 2000 Service Pack 4')

That doesn't group by displayname
 
Upvote 0 Downvote
That gives a cumulative total for all the machineid's. I need to know the total for each of the Displayname00 individually.

What you suggested would work if I did something like this only for each of the displayname00's. I would then have around 32 individual queries.

Code: 
SELECT COUNT(MachineID)
FROM   Add_or_Remove_Program_DATA  
WHERE  (DisplayName00 = 'Windows 2000 Hotfix - KB824146') OR 
        (DisplayName00 = 'Windows XP Hotfix - KB824146') OR
 
Upvote 0 Downvote
What was wrong with your original query, with the order by taken off if you want? That should return the DisplayName00 and the count. Sorry if I'm being stupid but I really don't understand the problem.
 
Upvote 0 Downvote
Katy44, I don't think you're being stupid - I don't understand what is required either! ;-)

jmaclaurin, can you give an example of the result you want, eg a query like

Code: 
SELECT DisplayName00, COUNT(*)
FROM Add_or_Remove_Program_DATA
GROUP BY DisplayName00

will give a result something like this:

Code: 
Windows 2000 Hotfix - KB824146     | 6
Windows XP Hotfix - KB824146       | 3
Windows 2000 Hotfix - KB885250     | 10
...

--James
 
Upvote 0 Downvote
This is the result of the original query. Note how it is in alphabetical/numerical order.

Total Application
2336 Adobe Reader 7.0
2864 Microsoft Data Access Components KB870669
1723 Microsoft Office Outlook 2003
1882 Microsoft VGX Q833989
2830 Outlook Express Q837009
2235 Windows 2000 Hotfix - KB824146
1850 Windows 2000 Hotfix - KB824151
2226 Windows 2000 Hotfix - KB828028
2242 Windows 2000 Hotfix - KB828035
2231 Windows 2000 Hotfix - KB828741
2243 Windows 2000 Hotfix - KB835732
2231 Windows 2000 Hotfix - KB837001
1964 Windows 2000 Hotfix - KB840315
1932 Windows 2000 Hotfix - KB840987
1930 Windows 2000 Hotfix - KB841356
1932 Windows 2000 Hotfix - KB841533
1963 Windows 2000 Hotfix - KB841873
1361 Windows 2000 Hotfix - KB867282
1640 Windows 2000 Hotfix - KB873333
1642 Windows 2000 Hotfix - KB885250
1637 Windows 2000 Hotfix - KB888113
1758 Windows 2000 Hotfix - KB890175
144 Windows 2000 Hotfix - KB890859
1056 Windows 2000 Hotfix - KB890923
1744 Windows 2000 Hotfix - KB891711
1549 Windows 2000 Hotfix - KB891781
116 Windows 2000 Hotfix - KB892944
1120 Windows 2000 Hotfix - KB893066
149 Windows 2000 Hotfix - KB893086
1355 Windows 2000 Service Pack 4
2025 Windows Media Player 9 Hotfix [See KB885492 for more information]
887 Windows XP Hotfix - KB824146
894 Windows XP Hotfix - KB824151
905 Windows XP Hotfix - KB828028
905 Windows XP Hotfix - KB828035
904 Windows XP Hotfix - KB828741
897 Windows XP Hotfix - KB833987
904 Windows XP Hotfix - KB835732
904 Windows XP Hotfix - KB837001
901 Windows XP Hotfix - KB840315
896 Windows XP Hotfix - KB840987
895 Windows XP Hotfix - KB841356
896 Windows XP Hotfix - KB841533
901 Windows XP Hotfix - KB841873
768 Windows XP Hotfix - KB867282
850 Windows XP Hotfix - KB873333
896 Windows XP Hotfix - KB873376
852 Windows XP Hotfix - KB885250
848 Windows XP Hotfix - KB888113
890 Windows XP Hotfix - KB890175
124 Windows XP Hotfix - KB890859
666 Windows XP Hotfix - KB890923
881 Windows XP Hotfix - KB891711
825 Windows XP Hotfix - KB891781
119 Windows XP Hotfix - KB892944
674 Windows XP Hotfix - KB893066
125 Windows XP Hotfix - KB893086

I would like it to be sorted as I have it laid out in the query.
 
Upvote 0 Downvote
I would like it to look more like this. (I did this maually as an example)

Total Application
2336 Adobe Reader 7.0
2864 Microsoft Data Access Components KB870669
1723 Microsoft Office Outlook 2003
1882 Microsoft VGX Q833989
2830 Outlook Express Q837009
2235 Windows 2000 Hotfix - KB824146
887 Windows XP Hotfix - KB824146
1850 Windows 2000 Hotfix - KB824151
894 Windows XP Hotfix - KB824151
2226 Windows 2000 Hotfix - KB828028
905 Windows XP Hotfix - KB828028
2242 Windows 2000 Hotfix - KB828035
905 Windows XP Hotfix - KB828035
2231 Windows 2000 Hotfix - KB828741
904 Windows XP Hotfix - KB828741
2243 Windows 2000 Hotfix - KB835732
904 Windows XP Hotfix - KB835732
2231 Windows 2000 Hotfix - KB837001
904 Windows XP Hotfix - KB837001
1964 Windows 2000 Hotfix - KB840315
901 Windows XP Hotfix - KB840315
1932 Windows 2000 Hotfix - KB840987
896 Windows XP Hotfix - KB840987
1930 Windows 2000 Hotfix - KB841356
895 Windows XP Hotfix - KB841356
1932 Windows 2000 Hotfix - KB841533
896 Windows XP Hotfix - KB841533
897 Windows XP Hotfix - KB833987
1963 Windows 2000 Hotfix - KB841873
901 Windows XP Hotfix - KB841873
1361 Windows 2000 Hotfix - KB867282
768 Windows XP Hotfix - KB867282
1640 Windows 2000 Hotfix - KB873333
850 Windows XP Hotfix - KB873333
1642 Windows 2000 Hotfix - KB885250
852 Windows XP Hotfix - KB885250
1637 Windows 2000 Hotfix - KB888113
848 Windows XP Hotfix - KB888113
1758 Windows 2000 Hotfix - KB890175
890 Windows XP Hotfix - KB890175
144 Windows 2000 Hotfix - KB890859
124 Windows XP Hotfix - KB890859
1056 Windows 2000 Hotfix - KB890923
666 Windows XP Hotfix - KB890923
1744 Windows 2000 Hotfix - KB891711
881 Windows XP Hotfix - KB891711
2025 Windows Media Player 9 Hotfix [See KB885492 for more information]
1549 Windows 2000 Hotfix - KB891781
825 Windows XP Hotfix - KB891781
116 Windows 2000 Hotfix - KB892944
119 Windows XP Hotfix - KB892944
1120 Windows 2000 Hotfix - KB893066
674 Windows XP Hotfix - KB893066
149 Windows 2000 Hotfix - KB893086
125 Windows XP Hotfix - KB893086
1355 Windows 2000 Service Pack 4
 
Upvote 0 Downvote
Do you mean this?

Code: 
SELECT DisplayName00, COUNT(*)
FROM Add_or_Remove_Program_DATA
GROUP BY DisplayName00
ORDER BY CASE DisplayName00
  WHEN 'Adobe Reader 7.0' THEN 1
  WHEN 'Microsoft Data Access Components KB870669' THEN 2
  WHEN 'Microsoft Office Outlook 2003' THEN 3
  WHEN 'Windows 2000 Hotfix - KB824146' THEN 4
  ...
  END

--James
 
Upvote 0 Downvote
That produces a total of all Displayname00 items, not justthe ones specified.
 
Upvote 0 Downvote
Are you sure?
What happens if you do:
Code: 
SELECT COUNT(MachineID) AS Total, DisplayName00 AS Application  
FROM   Add_or_Remove_Program_DATA  
WHERE   (DisplayName00 = 'Adobe Reader 7.0') 
OR      (DisplayName00 = 'Microsoft Data Access Components KB870669')

Do you get them all or only the two specified?
 
Upvote 0 Downvote
That produces a total of all Displayname00 items, not just the ones specified
Click to expand...

Just filter your list then:

Code: 
SELECT DisplayName00, COUNT(*)
FROM Add_or_Remove_Program_DATA
WHERE DisplayName00 IN (
    'Adobe Reader 7.0',
    'Microsoft Data Access Components KB870669',
    'Microsoft Office Outlook 2003',
    'Windows 2000 Hotfix - KB824146',
    ...
  )
GROUP BY DisplayName00
ORDER BY CASE DisplayName00
  WHEN 'Adobe Reader 7.0' THEN 1
  WHEN 'Microsoft Data Access Components KB870669' THEN 2
  WHEN 'Microsoft Office Outlook 2003' THEN 3
  WHEN 'Windows 2000 Hotfix - KB824146' THEN 4
  ...
  END

--James
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

peac3
  • Locked
  • Question
Derive staus date time by aligning start and end datetime
Replies
7
Views
377
Andrzejek
Andrzejek
leifoet
  • Locked
  • Question
How te filter by date ?
Replies
9
Views
259
dhookom
dhookom
grnzbra
  • Locked
  • Question
Is "IF EXISTS()..." better than "IF (SELECT COUNT...) > 0"
Replies
4
Views
2K
gmmastros
gmmastros
f0rg3tfu1
  • Locked
  • Question
PLSQL Package Help
Replies
0
Views
329
f0rg3tfu1
f0rg3tfu1
peac3
  • Locked
  • Question
split one row into multiple rows... help pls 2
Replies
12
Views
756
Chris Miller
Chris Miller

Part and Inventory Search

Sponsor

Back
Top