Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

count groups

Posted on 2014-03-17
8
Medium Priority
?
183 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
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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 70

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
In this article I will describe the Copy Database Wizard 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.
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

572 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