Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53

    Unanswered: Select distinct count on multiple columns

    Hi!

    I'm trying to write a select statement that will pull a list of every client, their location, and if that location's status - along with a count of each client. And it has to be limited to the current pay period. I'm having a lot of trouble doing it.

    Ideally, it would look like this:
    Code:
    ClientName	Location		Status		ClientCount
    -------------------------------------------------------------------
    Go Lucky Toys	San Francisco, CA	PAID		3
    Go Lucky Toys	Seattle, WA		PAID		3
    Go Lucky Toys	Omaha, NE		NOT PAID	3
    Bob's Placemats	Williamsburg, VA	PAID		1
    Sandy's Candles	Portland, OR		PAID		2
    Sandy's Candles	Cincinnati, OH		PAID		2
    Tim's Tacks	Charleston, WV		NOT PAID	4
    Tim's Tacks	Austin, TX		PAID		4
    Tim's Tacks	New York, NY		PAID		4
    Tim's Tacks	Casper, WY		PAID		4
    So my pseudo-SQL would be along the lines of:
    SELECT DISTINCT ClientName, Location, Status, COUNT(DISTINCT ClientName|Location) AS ClientCount
    FROM tblInvoices
    WHERE PayPeriod = '07/15/2010'


    Could anyone help me out with this?

    Thanks!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You need to use GROUP BY instead of DISTINCT if you are using aggregate functions
    SQL GROUP BY Statement
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    I am using GROUP BY, actually - I left it out of my pseudo-SQL. My mistake. But it's still not returning the correct number.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Why did you post pseudo SQL when your error is in your actual SQL? I can't debug what you don't show me.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    Because I don't know how to write the actual SQL to pull this, hence me posting here in the first place.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm sorry but I'm afraid I am tired of playing guessing games on the forum.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by pootle flump View Post
    I'm sorry but ...
    don't be sorry!!

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    When people bring me roller skates and ask me to determine the problem with their truck, I usually suggest a guy down the road that is better with that kind of problem. He doesn't like me very much anymore.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    I'm sorry, I didn't see the sign to pay the trolls before crossing the bridge.

    You want the whole SQL? Here's the whole SQL:


    SELECT DISTINCT C.ClientName, C.ClientLocationOrBU, C.PayPeriodEndingDate, CASE
    (SELECT TOP 1 C1.StatusColor
    FROM T_ClientDataFeedStatus C1
    WHERE C1.ClientFeedType = 'HOURS' AND C1.ProcessingTarget = 'EMPLOYEES' AND C1.PayPeriodEndingDate = C.PayPeriodEndingDate AND
    C1.WorkWeekNumber = 1 AND C1.ClientName = C.ClientName AND C1.ClientLocationOrBU = C.ClientLocationOrBU)
    WHEN 'GREEN' THEN 1 WHEN 'YELLOW' THEN 2 WHEN 'RED' THEN 3 ELSE 0 END AS EmployeeWeek1, CASE
    (SELECT TOP 1 C1.StatusColor
    FROM T_ClientDataFeedStatus C1
    WHERE C1.ClientFeedType = 'HOURS' AND C1.ProcessingTarget = 'EMPLOYEES' AND C1.PayPeriodEndingDate = C.PayPeriodEndingDate AND
    C1.WorkWeekNumber = 2 AND C1.ClientName = C.ClientName AND C1.ClientLocationOrBU = C.ClientLocationOrBU)
    WHEN 'GREEN' THEN 1 WHEN 'YELLOW' THEN 2 WHEN 'RED' THEN 3 ELSE 0 END AS EmployeeWeek2, CASE
    (SELECT TOP 1 C1.StatusColor
    FROM T_ClientDataFeedStatus C1
    WHERE C1.ClientFeedType = 'HOURS' AND C1.ProcessingTarget = 'CONTRACTORS' AND C1.PayPeriodEndingDate = C.PayPeriodEndingDate AND
    C1.WorkWeekNumber = 1 AND C1.ClientName = C.ClientName AND C1.ClientLocationOrBU = C.ClientLocationOrBU)
    WHEN 'GREEN' THEN 1 WHEN 'YELLOW' THEN 2 WHEN 'RED' THEN 3 ELSE 0 END AS ContractorWeek1, CASE
    (SELECT TOP 1 C1.StatusColor
    FROM T_ClientDataFeedStatus C1
    WHERE C1.ClientFeedType = 'HOURS' AND C1.ProcessingTarget = 'CONTRACTORS' AND C1.PayPeriodEndingDate = C.PayPeriodEndingDate AND
    C1.WorkWeekNumber = 2 AND C1.ClientName = C.ClientName AND C1.ClientLocationOrBU = C.ClientLocationOrBU)
    WHEN 'GREEN' THEN 1 WHEN 'YELLOW' THEN 2 WHEN 'RED' THEN 3 ELSE 0 END AS ContractorWeek2, COUNT(DISTINCT C.ClientName + ' ' + C.ClientLocationOrBU) AS ClientCount
    FROM T_ClientDataFeedStatus C
    WHERE C.PayPeriodEndingDate = '07/15/2010'
    GROUP BY C.ClientName, C.ClientLocationOrBu, C.PayPeriodEndingDate

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Tarkon View Post
    Here's the whole SQL:
    this doesn't resemble the output you posted, there are extraneous columns

    what was your original question again? you said "Could anyone help me out with this?" but you didn't actually say what the problem is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    -- This is a bit of a guess based on your very wooly requirements so far.
    -- You will undoubtedly get a better answer if you provide your CREATE TABLE statements along with sample data.
    -- We don't understand your data as well as you!
    -- Oh and don't forget SQL Server version too...
    
    -- Basic deets
    SELECT ClientName
         , Location
         , Status
    FROM   tblInvoices
    WHERE  PayPeriod = '20100715'
    
    
    -- Count by ClientName and Location
    SELECT ClientName
         , Location
         , Count(*) As ClientCount
    FROM   tblInvoices
    -- I don't know if you want the count in the pay period or not...
    --WHERE  PayPeriod = '20100715'
    GROUP
        BY ClientName
         , Location
    
    -- Combine the two
    SELECT deets.ClientName
         , deets.Location
         , deets.Status
         , counts.ClientCount
    FROM   (
            SELECT ClientName
                 , Location
                 , Status
            FROM   tblInvoices
            WHERE  PayPeriod = '20100715'
           ) As deets
     INNER
      JOIN (
            SELECT ClientName
                 , Location
                 , Count(*) As ClientCount
            FROM   tblInvoices
            -- I don't know if you want the count in the pay period or not...
            --WHERE  PayPeriod = '20100715'
            GROUP
                BY ClientName
                 , Location
           ) As counts
        ON counts.ClientName = deets.ClientName
       AND counts.Location = deets.Location
    George
    Home | Blog

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry, my internet access was down for a bit.

    Hey, trolls get hungry too! If you think that we're trolls, you really need to get out more.

    This is not a final solution, just a test to see if I'm on the right track and getting decent performance:
    Code:
    SELECT C.ClientName, C.ClientLocationOrBU, C.PayPeriodEndingDate
    ,  MAX(CASE 
          WHEN 1 = c.WorkWeekNumber
             AND 'EMPLOYEES' = c.ProcessingTarget 
             AND 'HOURS' = c.ClientFeedType 
             THEN c.StatusColor END) AS EmployeeWeek1
    ,  MAX(CASE 
          WHEN 2 = c.WorkWeekNumber
             AND 'EMPLOYEES' = c.ProcessingTarget 
             AND 'HOURS' = c.ClientFeedType 
             THEN c.StatusColor END) AS EmployeeWeek2
    ,  MAX(CASE 
          WHEN 1 = c.WorkWeekNumber
             AND 'CONTRACTORS' = c.ProcessingTarget 
             AND 'HOURS' = c.ClientFeedType 
             THEN c.StatusColor END) AS ContractorWeek1
    ,  MAX(CASE 
          WHEN 2 = c.WorkWeekNumber
             AND 'CONTRACTORS' = c.ProcessingTarget 
             AND 'HOURS' = c.ClientFeedType 
             THEN c.StatusColor END) AS ContractorWeek2
    ,  COUNT(DISTINCT C.ClientName + ' ' + C.ClientLocationOrBU) AS ClientCount
       FROM T_ClientDataFeedStatus C
       WHERE C.PayPeriodEndingDate = '07/15/2010'
       GROUP BY C.ClientName, C.ClientLocationOrBu, C.PayPeriodEndingDate
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  13. #13
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    We're having fits with internet connectivity, but this should produce the same results that you had before but a lot faster.
    Code:
    SELECT C.ClientName, C.ClientLocationOrBU, C.PayPeriodEndingDate
    ,  Coalesce(Max(CASE 
          WHEN 1 = c.WorkWeekNumber
             AND 'EMPLOYEES' = c.ProcessingTarget 
             AND 'HOURS' = c.ClientFeedType 
             THEN n.num 
          END), 0) AS EmployeeWeek1
    ,  Coalesce(Max(CASE 
          WHEN 2 = c.WorkWeekNumber
             AND 'EMPLOYEES' = c.ProcessingTarget 
             AND 'HOURS' = c.ClientFeedType 
             THEN n.num
          END), 0) AS EmployeeWeek2
    ,  Coalesce(Max(CASE 
          WHEN 1 = c.WorkWeekNumber
             AND 'CONTRACTORS' = c.ProcessingTarget 
             AND 'HOURS' = c.ClientFeedType 
             THEN n.num
          END), 0) AS ContractorWeek1
    ,  Coalesce(Max(CASE 
          WHEN 2 = c.WorkWeekNumber
             AND 'CONTRACTORS' = c.ProcessingTarget 
             AND 'HOURS' = c.ClientFeedType 
             THEN n.num
          END), 0) AS ContractorWeek2
    ,  COUNT(DISTINCT C.ClientName + ' ' + C.ClientLocationOrBU) AS ClientCount
       FROM T_ClientDataFeedStatus C
       LEFT JOIN (SELECT 1 AS num, 'GREEN' AS color UNION
          SELECT 2, 'YELLOW' UNION
          SELECT 3, 'RED') AS n
          ON (n.color = c.StatusColor)
       WHERE C.PayPeriodEndingDate = '07/15/2010'
       GROUP BY C.ClientName, C.ClientLocationOrBu, C.PayPeriodEndingDate
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  14. #14
    Join Date
    Sep 2003
    Location
    Washington, DC - USA
    Posts
    53
    Thanks for the replies! I've tried your suggestions, but unfortunately it's coming back with a client count of one for each of them

    Here's my CREATE TABLE:
    Code:
    CREATE TABLE [dbo].[T_ClientDataFeedStatus](
    	[ClientName] [varchar](64) NULL,
    	[ClientLocationOrBu] [varchar](128) NULL,
    	[ClientFeedType] [varchar](16) NULL,
    	[ProcessingTarget] [varchar](16) NULL,
    	[PayPeriodEndingDate] [smalldatetime] NULL,
    	[WorkWeekNumber] [int] NULL,
    	[UploadedBy] [varchar](32) NULL,
    	[UploadedAt] [smalldatetime] NULL,
    	[StatusColor] [varchar](16) NULL,
    	[ZipFilename] [varchar](256) NULL
    ) ON [PRIMARY]
    and here's the contents of the table for the pay period in question:
    Code:
    ClientName	ClientLocationOrBu	ClientFeedType	ProcessingTarget	PayPeriodEndDate	WorkWeekNumber	UploadedBy	UploadedAt	StatusColor	ZipFilename
    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Go Lucky Toys	San Francisco, CA	HOURS		EMPLOYEES		7/15/2010 0:00		1		2131		7/12/2010 7:35	YELLOW		100712071351-612073475
    Go Lucky Toys	Seattle, WA		HOURS		EMPLOYEES		7/15/2010 0:00		1		2131		7/12/2010 11:34	GREEN		100712111702-612073478
    Go Lucky Toys	Seattle, WA		HOURS		EMPLOYEES		7/15/2010 0:00		1		2131		7/12/2010 11:51	GREEN		100712113424-612073478
    Go Lucky Toys	Omaha, NE		HOURS		EMPLOYEES		7/15/2010 0:00		1		2131		7/12/2010 13:04	GREEN		100712121611-612073481
    Bob's Placemats	Saginaw, MI		HOURS		EMPLOYEES		7/15/2010 0:00		1		2131		7/12/2010 14:12	GREEN		100712134931-612073485
    Sandy's Candles	Williamsburg, VA	HOURS		EMPLOYEES		7/15/2010 0:00		1		2131		7/12/2010 15:57	GREEN		100712151305-612073489
    Go Lucky Toys	Seattle, WA		HOURS		EMPLOYEES		7/15/2010 0:00		1		2131		7/12/2010 9:21	GREEN		100712084406-612073475
    Sandy's Candles	Williamsburg, VA	HOURS		CONTRACTORS		7/15/2010 0:00		1		2012		7/14/2010 10:15	GREEN		100714093934-612073510
    Go Lucky Toys	Omaha, NE		HOURS		CONTRACTORS		7/15/2010 0:00		1		2012		7/14/2010 11:41	GREEN		100714101638-612073510
    Go Lucky Toys	Portland, OR		HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/19/2010 9:54	GREEN		100719093357-612073534
    Go Lucky Toys	Cincinnati, OH		HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/19/2010 11:16	GREEN		100719105300-612073534
    Bob's Placemats	Saginaw, MI		HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/19/2010 11:58	GREEN		100719111817-612073534
    Sandy's Candles	Williamsburg, VA	HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/19/2010 17:07	GREEN		100719163504-612073553
    Go Lucky Toys	Charleston, WV		HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/19/2010 17:55	GREEN		100719172025-612073558
    Go Lucky Toys	Austin, TX		HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/20/2010 6:58	GREEN		100720064615-612073567
    Go Lucky Toys	New York, NY		HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/20/2010 7:31	GREEN		100720071918-612073567
    Go Lucky Toys	Seattle, WA		HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/20/2010 8:02	YELLOW		100720073725-612073567
    Sandy's Candles	Williamsburg, VA	HOURS		CONTRACTORS		7/15/2010 0:00		2		2012		7/21/2010 8:04	GREEN		100721073930-612073607
    Go Lucky Toys	Charleston, WV		HOURS		CONTRACTORS		7/15/2010 0:00		2		2012		7/21/2010 8:31	GREEN		100721081240-612073607
    Bob's Placemats	Saginaw, MI		HOURS		CONTRACTORS		7/15/2010 0:00		2		2012		7/21/2010 9:02	GREEN		100721083255-612073607
    Go Lucky Toys	Casper, WY		HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/19/2010 12:58	GREEN		100719124542-612073534
    Go Lucky Toys	Charleston, WV		HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/19/2010 13:55	GREEN		100719132040-612073534
    Sandy's Candles	Williamsburg, VA	HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/19/2010 15:22	YELLOW		100719141826-612073534
    Go Lucky Toys	Knoxville, TN		HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/20/2010 6:45	GREEN		100720063448-612073567
    Go Lucky Toys	Seattle, WA		HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/20/2010 7:10	GREEN		100720065958-612073567
    Sandy's Candles	Phoenix, AZ		HOURS		EMPLOYEES		7/15/2010 0:00		1		2131		7/12/2010 6:42	GREEN		100712062958-612073475
    Go Lucky Toys	San Francisco, CA	HOURS		EMPLOYEES		7/15/2010 0:00		1		2131		7/12/2010 7:53	GREEN		100712073904-612073475
    Go Lucky Toys	Seattle, WA		HOURS		EMPLOYEES		7/15/2010 0:00		1		2131		7/12/2010 8:19	GREEN		100712075915-612073475
    Go Lucky Toys	Las Vegas, NV		HOURS		EMPLOYEES		7/15/2010 0:00		1		2131		7/12/2010 10:49	GREEN		100712102812-612073478
    Go Lucky Toys	Seattle, WA		HOURS		EMPLOYEES		7/15/2010 0:00		1		2131		7/12/2010 11:15	GREEN		100712105153-612073478
    Go Lucky Toys	Seattle, WA		HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/19/2010 10:24	GREEN		100719095847-612073534
    Go Lucky Toys	Seattle, WA		HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/19/2010 12:29	GREEN		100719121014-612073534
    Go Lucky Toys	Atlanta, GA		HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/19/2010 13:18	GREEN		100719125754-612073534
    Go Lucky Toys	Tampa, FL		HOURS		EMPLOYEES		7/15/2010 0:00		1		2131		7/12/2010 10:27	GREEN		100712100440-612073478
    Go Lucky Toys	Knoxville, TN		HOURS		EMPLOYEES		7/15/2010 0:00		1		2131		7/12/2010 7:12	GREEN		100712064709-612073475
    Go Lucky Toys	Oklahoma City, OK	HOURS		EMPLOYEES		7/15/2010 0:00		1		2131		7/12/2010 8:40	GREEN		100712082556-612073475
    Bob's Placemats	Saginaw, MI		HOURS		CONTRACTORS		7/15/2010 0:00		1		2012		7/14/2010 12:30	GREEN		100714114717-612073510
    Go Lucky Toys	Seattle, WA		HOURS		EMPLOYEES		7/15/2010 0:00		2		2131		7/19/2010 10:51	GREEN		100719102743-612073534

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Tarkon View Post
    Thanks for the replies! I've tried your suggestions, but unfortunately it's coming back with a client count of one for each of them
    That is because the ClientName occurs in both the GROUP BY and in the Count() too. Use Count(*) instead.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •