dcsimg
Results 1 to 19 of 19

Thread: [RESOLVED] break down transactions in sold units.

  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,406

    Resolved [RESOLVED] break down transactions in sold units.

    Code:
    transcount	TransNumber	grossvalue	ticketcounter
    2	1041441	15,00	1
    I want to have if transcount =1 then one group
    If transcount =2 then another group ..

    If transcount >=5 then another group

    the group will have the summed amount of grossvalue per group and the ticket transactions count (I just used ticketcounter that will always be 1 so I can do a simple count (ticketcounter) for ease.

    thanks
    Slow as hell.

  2. #2

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,406

    Re: break down transactions in sold units.

    I was thinking this:
    Code:
    select transcount,sum(grossvalue),count(ticketcounter)  from tickets
    group by transcount
    order by transcount
    This seems to split it, now I need to sum the >= 5 transcounts...
    Slow as hell.

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,406

    Re: break down transactions in sold units.

    What do you mean ?

    The problem is the case so I can do the >=5 to a different group.
    I'm struggling a little with this.
    Slow as hell.

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,586

    Re: break down transactions in sold units.

    I see. Is the database SQL Server? I have an idea but I'm not sure that the syntax is valid for other databases.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,787

    Re: break down transactions in sold units.

    You know you can do this

    Code:
    Select TransCount,Sum(GrossValue), Sum(TicketCounter) From SomeTable Group by TransCount
    Just change it to this

    Code:
    Select Case When TransCount in (1,2,3,4) Then TransCount When TransCount>=5 Then 5 Else ?? End
              ,Sum(GrossValue), Sum(TicketCounter) From SomeTable 
        Group by Select Case When TransCount in (1,2,3,4) Then TransCount When TransCount>=5 Then 5 Else ?? End
    You can make whatever CASE formula you want in the SELECT list - just copy/paste it into the GROUP BY.

    I was unclear on what to do with TransCount of 3 and 4 - you failed to mention those. And also unclear on what might happen after >=5 - like do you have a >= 10? Are we doing a DIVIDE by 5 for these upper groups?

    Either way, it just complicates the CASE a bit more.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  6. #6
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    31,943

    Re: break down transactions in sold units.

    if it's SQL Server, you can group by a case statement....

    Code:
    group by
      case 
        when transCount >= 5 then 5
        else transCount
      end
    And you can order by the same as well too:
    Code:
    order by
      case 
        when transCount >= 5 then 5
        else transCount
      end
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  7. #7

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,406

    Re: break down transactions in sold units.

    I'm trying!
    I guess it the late brainfart time.

    Code:
    select case when transcount >=5 then  5 ELSE transcount  END 
    ,sum(grossvalue) as TotalGross,count(ticketcounter) as TotalTicketTransactions 
    from tickets
    group by case when transcount >=5 then  5 ELSE transcount  END 
    order by transcount
    Says I don't have transcount in a group by clause.
    Slow as hell.

  8. #8

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,406

    Re: break down transactions in sold units.

    FFFF.
    It was the order by.
    Sorry too tired.

    Edit.
    Would a rank function work also?
    Slow as hell.

  9. #9
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,586

    Re: break down transactions in sold units.

    The ORDER BY clause is based on the columns in the result set, not the columns in the table. You need to alias that first column and use that alias in the ORDER BY clause, e.g.
    Code:
    SELECT
        CASE
            WHEN transcount >=5 THEN 5
            ELSE transcount
        END AS TransCount,
        SUM(grossvalue) AS TotalGross,
        COUNT(ticketcounter) AS TotalTicketTransactions 
    FROM tickets
    GROUP BY
        CASE
            WHEN transcount >=5 THEN 5
            ELSE transcount
        END
    ORDER BY TransCount
    You may want to use an alias different to the table column name to avoid confusion:
    Code:
    SELECT
        CASE
            WHEN transcount >=5 THEN 5
            ELSE transcount
        END AS GroupedTransCount,
        SUM(grossvalue) AS TotalGross,
        COUNT(ticketcounter) AS TotalTicketTransactions 
    FROM tickets
    GROUP BY
        CASE
            WHEN transcount >=5 THEN 5
            ELSE transcount
        END
    ORDER BY GroupedTransCount
    Last edited by jmcilhinney; Oct 18th, 2018 at 09:02 AM.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  10. #10
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    40,341

    Re: break down transactions in sold units.

    You can also cheat a bit with the Order By, because you don't actually have to specify field names etc, you can simply specify the position of the field within the Select clause, eg: "ORDER BY 1" will order by the first field

    Using an Alias is preferable tho, as it is self-documenting.

  11. #11

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,406

    Re: break down transactions in sold units.

    Hi.
    Yes for the correction.
    I've fixed it.

    I was thinking to try rank. Is it possible? don't want the code just if it's a go so I can give it a try tomorrow.

    Thanks.
    Slow as hell.

  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,787

    Re: break down transactions in sold units.

    Just to be complete you could just ORDER BY the same CASE statement that is in the SELECT list and the GROUP BY.

    And please note that even though you are repeating the SAME "formula" in 3 places do not think that it's going to re-evaluate that formula THREE times - it's all cool and fast regardless of how you do the syntax.

    Of course it could be said that "repeating" the CASE statement when you could just ALIAS or use the ORDINAL POSITION of "1" is bad technique - but then again you HAVE to repeat in the GROUP BY anyway.

    [edit] btw - I'm totally fine with using ORDER BY 1 - that's just as self documenting, imo, as making up a column name and using that. ORDINAL position for ORDER BY ok by me![/edit]

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #13
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,586

    Re: break down transactions in sold units.

    Quote Originally Posted by szlamany View Post
    btw - I'm totally fine with using ORDER BY 1 - that's just as self documenting, imo, as making up a column name and using that. ORDINAL position for ORDER BY ok by me!
    I guess the only small issue I would have with doing that is that you don't know what column you're ordering by just by looking at the ORDER BY clause; you have to look at the SELECT clause to see what column is first whole an alias should be descriptive enough that it' is all you need. Not exactly an onerous task, mind you.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  14. #14
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,787

    Re: break down transactions in sold units.

    You can also be burned by using a COLUMN NAME alias and not realize it.

    Code:
    Select Convert(varchar(10),OrderDate,101) "OrderDate", field2, field3
       From SomeTable
       Order by OrderDate
    This ORDER BY will unfortunately use the CONVERT()'ed date value - and since that is DD/MM/YYYY - it will sort by DD first and not YYYY first - major fail.

    You can get around that by using Order by SomeTable.OrderDate or by giving SomeTable and alias (WHICH I ALWAYS DO) and then use that in the order by

    Code:
    Select Convert(varchar(10),OrderDate,101) "OrderDate", field2, field3
       From SomeTable ST
       Order by ST.OrderDate
    Since I always use TABLE ALIAS's I am immune to this issue - I ALWAYS order by ALIAS.COLUMNNAME syntax. And to further that, since my next option is to use ORDINAL position, I'm always in strict control over the ORDER BY.

    [sidenote] Did you ever forget to specify an ORDER BY and years go by with no issues - default SORT order seems to be working (whatever table scan or index scan/seek supplied a nice order). Then some source table in the query grows past some magic size and the order by all of a sudden becomes somewhat random. Support call from client comes next. [/sidenote]
    Last edited by szlamany; Oct 18th, 2018 at 09:58 AM.

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  15. #15

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,406

    Re: break down transactions in sold units.

    At the last comment of szlamany, not the order by but in general on forgetting things, we had an issue like so but the problem was that we have switched to a new software provider a couple of years ago.
    The old software provider had a different database with completely different id's.
    At some point one out of a million records matched the old and new database id.
    I was struggling with this for a week before I could find what was going on since the old and new databases where merged in one big database but someone forgot to specify something like:
    And newdbid <> olddbid . That someone left the company a couple of years before I get there so I could not find him to ask what was going on.
    Slow as hell.

  16. #16
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,423

    Re: break down transactions in sold units.

    Hi,

    not sure if I understand you request, but I'll give it a try.

    this sql will count all Orders and Group by Country
    from all Orders it will return the Count(GroupCount) for Company(s) which
    starts will Letter A
    the Sum of all Orders in that Country is also returned

    sql Statment
    Code:
    TRANSFORM Count(Orders.OrderID) AS [CountOfOrder ID]
    SELECT Customers.Country, Count(Orders.OrderID) AS [Country Total], Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount]))) AS Gesamt
    FROM Customers INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Customers.CustomerID = Orders.CustomerID
    GROUP BY Customers.Country
    ORDER BY Customers.Country
    PIVOT IIf([Customers].[CompanyName] Like 'A*','A','B-Z');
    image results
    Name:  qryCount.JPG
Views: 58
Size:  52.6 KB

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  17. #17

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,406

    Re: break down transactions in sold units.

    OMG is this Access?!
    Thanks but no thanks
    Slow as hell.

  18. #18
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,423

    Re: break down transactions in sold units.

    Quote Originally Posted by sapator View Post
    OMG is this Access?!
    Thanks but no thanks
    I know you are not going to use Access, the hint was to use PIVOT to group Data

    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  19. #19

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,406

    Re: break down transactions in sold units.

    Ah , I see.
    Thanks.
    Slow as hell.

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width