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 :$