?
Solved

count groups

Posted on 2014-03-17
8
Medium Priority
?
174 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:Seven price
[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
  • 2
  • 2
  • +1
8 Comments
 
LVL 66

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:Seven price
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 66

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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 9

Author Comment

by:Seven price
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 49

Accepted Solution

by:
PortletPaul earned 2000 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:Seven price
ID: 39937681
tks
0
 
LVL 49

Expert Comment

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

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

762 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