SQL Query Halp - Combining inside a Group By

[s0me0nesmind1]

Bleh, wasn't sure where to ask so why not OT?

Anyhow, I hate integrated queries - they are basically my cryptonite.

Looking for some help on how to Group By in conjunction with combining the grouped by items. Any chance someone would be able to help here?
Example below: Multiple transactions in the same state with transaction amounts. I want to group transactions A+B together, but keep C transactions separate.​

Data:​

Transaction Type, State, Amount
A, SC,43.00
B, SC,44.00
C, SC,45.00
B, SC,46.00

What I want the output to look like is:​

A+B, SC,133.00
C, SC,45.00

 

[lozina]

I dunno, there is a programming sub forum though

My quick and dirty solution?

Maybe use an inner select to combine the A's and B's ?

select type, state, sum(amount) from (
select case when type = 'C' then 'C' else 'A+B' end as type, state, amount
) x
group by type, state

 

[reallyscrued]

My SQL is not bad...but I can't understand what you want your final output to be.

Programming fourm would be the place to ask however.

 

[s0me0nesmind1]

I dunno, there is a programming sub forum though

My quick and dirty solution?

Maybe use an inner select to combine the A's and B's ?

select type, state, sum(amount) from (
select case when type = 'C' then 'C' else 'A+B' end as type, state, amount
) x
group by type, state

Access isn't liking that - I guess it doesn't like the Query after the FROM statement? Says "The Microsoft Access Database Engine Could Not find the object "CASE WHEN [Type] = blah blah"

 

[lozina]

Access isn't liking that - I guess it doesn't like the Query after the FROM statement? Says "The Microsoft Access Database Engine Could Not find the object "CASE WHEN [Type] = blah blah"

ah, yeah I dont think Access supports case statements you have to substitute with iif i think, and my code example was pseudo code for example i didnt specify a from for the inner statement just serves as an example.

with access maybe:


select type, state, sum(amount) from (
select
iif(type = 'C', 'C', 'A+B') as type, state, amount from tablename
) x
group by type, state

 

[richardycc]

if there is only 3 types, you can do union.
SELECT type, SUM(sales) as "Total sales"
FROM order_details
where type in ('a','b')
GROUP BY type
union
SELECT type, SUM(sales) as "Total sales"
FROM order_details
where type = 'c'
GROUP BY type

 

[Jaepheth]

select 'A+B', state, sum(amount) from table_name where trans_type in ('A','B') group by state
union
select trans_type, state, sum(amount) from table_name where trans_type = 'C' group by state;

EDIT: oops, need to group by state and trans_type too.

select 'A+B', state, sum(amount) from table_name where trans_type in ('A','B') group by state, trans_type
union
select trans_type, state, sum(amount) from table_name where trans_type = 'C' group by state, trans_type;

 

[darkewaffle]

Could make a query using a calculated field to "class" A/B/C with an if statement and then work off those results.

Something like,

Typeclass: if(type = a, ACtype, if(type = b, Btype, if(type = c, ACtype, "Invalid type")))

So you'd have a query with the following data

Transaction Type, State, Amount, typeclass
A, SC,43.00, ACtype
B, SC,44.00, Btype
C, SC,45.00, ACtype
B, SC,46.00, Btype

And then just calculate/group by the 'class' instead of the 'type'. Though if you have a lot more types than just ABC / or if they change often / or if this precludes some level of detail you need on your output this may not work.

 

[s0me0nesmind1]

ah, yeah I dont think Access supports case statements you have to substitute with iif i think, and my code example was pseudo code for example i didnt specify a from for the inner statement just serves as an example.

with access maybe:


select type, state, sum(amount) from (
select
iif(type = 'C', 'C', 'A+B') as type, state, amount from tablename
) x
group by type, state

Getting closer it looks like:

Still coming up short with "You tried to execute a query that does not include the specified expression "IIF(table.[Type]='C',table.[Type]='C',table.[Type]='AB')" as part of an aggregate function. :twisted:

 

[s0me0nesmind1]

select 'A+B', state, sum(amount) from table_name where trans_type in ('A','B') group by state
union
select trans_type, state, sum(amount) from table_name where trans_type = 'C' group by state;

EDIT: oops, need to group by state and trans_type too.

select 'A+B', state, sum(amount) from table_name where trans_type in ('A','B') group by state, trans_type
union
select trans_type, state, sum(amount) from table_name where trans_type = 'C' group by state, trans_type;

... Sorry bud, all I seem to be able to get with this is that they are adding all 3 up into 1 column.

 

[Jaepheth]

... Sorry bud, all I seem to be able to get with this is that they are adding all 3 up into 1 column.

sorry, only have to group by trans_type for the C query

made a sample in Access:

trans_type	state	amount
A		TX		1
B		TX		1
C		TX		1
A		OK		1
B		OK		1
C		OK		1
A		AL		1
B		AL		1
C		AL		1

select 'A+B', state, sum(amount) from table1 where trans_type in ('A','B') group by state
union
select trans_type, state, sum(amount) from table1 where trans_type = 'C' group by state, trans_type;

Yields:

Expr1000	state	Expr1002
A+B		AL		2
A+B		OK		2
A+B		TX		2
C		AL		1
C		OK		1
C		TX		1

 

[s0me0nesmind1]

sorry, only have to group by trans_type for the C query

made a sample in Access:

trans_type	state	amount
A		TX		1
B		TX		1
C		TX		1
A		OK		1
B		OK		1
C		OK		1
A		AL		1
B		AL		1
C		AL		1

select 'A+B', state, sum(amount) from table1 where trans_type in ('A','B') group by state
union
select trans_type, state, sum(amount) from table1 where trans_type = 'C' group by state, trans_type;

Yields:

Expr1000	state	Expr1002
A+B		AL		2
A+B		OK		2
A+B		TX		2
C		AL		1
C		OK		1
C		TX		1

Looks like that is working, thanks bud! <3 Hopefully I can get stuff like this down better without having to ask :$