How do you combine 2 items in a Group By while doing a Crosstab query?

[anoble1]

I have a Crosstab query that I am working on. So, for my Column Heading I want to use this 1 Field that has 5 options in it. Right now if I put in the Criteria "Car" or "Truck" I get 2 columns, 1 column for car and 1 column for truck. Is there a way I can just total those both up in 1 column. So the Field will show a total of Car & Truck instead of separating it?

[NeoPa]

Hi ANoble.

I suspect there is a way. Why don't you explain what you're doing with all the relevant detail and then the answer will make a lot more sense. We can express it in terms that you, and others reading the question, will be able to understand more easily.

[anoble1]

Please see the attached. Right now I am having to separate in the Group By in the Crosstab query with the criteria Car or Truck.
On the right in the attachment you will see Proposed where I would like to Sum those 2 selections in the "Type" field.

TRANSFORM Count(tblInformation.[Dealer Name]) AS [CountOfDealer Name]
SELECT tblInformation.[State]
FROM tblInformation
WHERE (((tblInformation.[Type])="Car" Or (tblInformation.[Type])="Truck"))
GROUP BY tblInformation.[State]
PIVOT tblInformation.[Type];

[IMGNOTHUMB]https://bytes.com/attachments/attachment/10591d166723925 7/annotation-2022-10-31-130030.png[/IMGNOTHUMB]

[NeoPa]

It seems like you don't need a Crosstab at all then if I understand you correctly. What about :

SELECT   [State]
       , Count([Type]) AS [Car/Truck]
FROM     [tblInformation]
WHERE    [Type] In('Car','Truck')
GROUP BY [State]
ORDER BY [State]

[anoble1]

My fault there. Later once I get this figured out I need to add another field and do the same type thing beside the “Type” field

[NeoPa]

If you need multiple sets of groupings within your possible [Type] columns then Crosstab on a subquery that connects the values you require :

SELECT [State]
     , Switch([Type]='Car','Car/Truck'
                    ,'Truck','Car/Truck'
                    ,'Motorcycle','Bike'
                    ,'Scooter','Bike'
                    ,True,[Type]) As [NewType]
     , [OtherField(s)]
FROM   [tblInformation]

You're right that the answer will change when the question does, so we do need to know the question in order to give the most appropriate answer. The latter suggestion should work in most cases but the earlier suggestion will only work in the scenario as previously described. It's more straightforward though so would be preferable in situations where it works.

[Arushi]

You cannot combine two items in a Group By while doing a Crosstab query.

[NeoPa]

Arushi:
You cannot combine two items in a Group By while doing a Crosstab query.

I'm afraid I don't even know what you're trying to say :-S

Is this a response to anything (any post?) in particular?

Are you saying that PIVOT & GROUP BY clauses can never be used together in the same set of SQL? That would appear to be wrong if so.