Solved

count groups

Posted on 2014-03-17
8
170 Views
Last Modified: 2014-03-18
if I have a group for example in my column
coumn1 coulmn 2
myg1         1
myg1         1
myg1         1
mygg         2
mygg         2
mygh         3
mygh         3

in my select what is the syntax to display in column 2
0
Comment
Question by:sevensnake77
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39935267
Give us a data mockup of what you're trying to pull off here, as the above description needs more detail.

If you're just trying to return the counts of myg1's, mygg's, mygh's, etc.
SELECT column1, COUNT(column2) as the_count
FROM your_table
GROUP BY column1
ORDER BY column1

Open in new window

0
 
LVL 9

Author Comment

by:sevensnake77
ID: 39935286
Yes I tried that, already but I thought there maybe another syntax than count, count does not work  for me.  The groups sometime repeat but I do not want the count to repeat.  maybe like compare syntax if there is one.
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39935325
Give us a data mockup of what you're trying to pull off here, both source and desired output, as the above description needs more detail.

While you're at it, show us the T-SQL you have so far.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 9

Author Comment

by:sevensnake77
ID: 39935398
Its just a basic select, But the column I am selecting have Distributor names that are assigned to a upc code. Basically for the most part the upc codes are grouped together and the distributor names are also. Some distributor names run the same for most. I just basically trying to alternate colors in a crystal report but I cannot do it using text it has to be numeric.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 39935676
Try this:

SELECT
    column1, COUNT(DISTINCT column2) AS column2_count
FROM ...
GROUP BY
    column1
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39935777
>>" I just basically trying to alternate colors in a crystal report but I cannot do it using text it has to be numeric. "

I'm having some trouble understanding what you are really wanting. Until I read that quoted sentence I thought Scott was on the money, but now I'm not so sure. The result of count(x) or count(distinct x) is not predictable and they could be the same values for different distributors; hence it is possible that you would not get a change of colour between consecutive distributors. Just imagine if every count(distinct x) returned the value 1; this would not meet your purpose I believe.

Q1: If there are 200 different distributors do you want every integer between 1 & 200 in that column?

Q2: Would you then just base your colour changes on odd -v- even values of that column?

Q3: Is the implied order by Distributor?


Assuming YES to my 3 question, my approach on the solution would be to use DENSE_RANK() over the results of your current query. Something like this:
SELECT
      innerq.distributor
    , innerq.sum_thing
    , DENSE_RANK() OVER (ORDER BY innerq.distributor ASC) as color_flag
FROM (
       -- assumed as your existing query, now as a subquery
            SELECT
                  distributor
                , SUM(thing) AS sum_thing
            FROM sometable
            GROUP BY
                  distributor
      ) AS innerq

Open in new window

tip: For the fastest results in sql questions, provide these:
1. sample data (from each of the tables involved)
2. expected results
0
 
LVL 9

Author Closing Comment

by:sevensnake77
ID: 39937681
tks
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39938275
:) I guessed well then. Thanks and cheers, Paul
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
In this article I will describe the Backup & Restore 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.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

789 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