Solved

count groups

Posted on 2014-03-17
8
172 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
[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 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
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql query 5 55
grouping by date only 6 22
Using asp.net to develop android, ios, windows APPS 7 46
What does "Between" mean? 6 36
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.

738 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