Solved

count groups

Posted on 2014-03-17
8
171 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

735 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