Display results from multiple groupings in one query

49
January 11, 2019, at 1:30 PM

I have a queries similar to below:

select item, count(item)
from items
group by item
select item2, count(item2)
from items
group by item2

I have similar queries to above where I fetch different data from the same table. I want to know if I can join those queries together into a single query rather then sending individual requests to the database?

This is the solution to date that I have come up with:

select item, count(item), "item2", -1
from items
group by item
union
select "item", -1, item2, count(item2)
from items
group by item2

This way we have a column for each data. Using PHP (or other programming language) I can figure out to which select current row is for, i.e. if row 1 in column 1 has a number then we are handling data for the first select statement.

Example mysql output:

item \ count(item) \ item2 \ -1
0    \ 2           \ item2 \ -1
1    \ 5           \ item2 \ -1
item \ -1          \ 0     \ 3
item \ -1          \ 1     \ 7

I dont like that I am having to pre-define columns that I need to display; i dont see this saving me any time; I might as well stick with multiple queries in this scenario.

READ ALSO
How do query and update MySQL hosted on AWS RDS from POSTMAN before sending a post request [on hold]

How do query and update MySQL hosted on AWS RDS from POSTMAN before sending a post request [on hold]

I am trying to query Database to check if the user has a certain restriction before sending a Create/Post request from Postman

48
Isset works on Local server, but it doesn't on live server

Isset works on Local server, but it doesn't on live server

I'm developing website on my local using Xampp v32

66
doubts about write barrier as for java garbage collection

doubts about write barrier as for java garbage collection

I'm reading articles about Java GCI know write barrier is used when reference updated

144
add zero to each variable having a single number

add zero to each variable having a single number

I need to add zero to each variable if it is a single number

106