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

1. ## [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

2. ## 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...

3. ## 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.

4. ## 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.

5. ## 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.

6. ## 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

7. ## 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.

8. ## Re: break down transactions in sold units.

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

Edit.
Would a rank function work also?

9. ## 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```

10. ## 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. ## 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.

12. ## 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.

 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]

13. ## Re: break down transactions in sold units.

Originally Posted by szlamany
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.

14. ## 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]

15. ## 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.

16. ## 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

regards
Chris

17. ## Re: break down transactions in sold units.

OMG is this Access?!
Thanks but no thanks

18. ## Re: break down transactions in sold units.

Originally Posted by sapator
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

19. ## Re: break down transactions in sold units.

Ah , I see.
Thanks.

#### 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