CODEWITHSUNDEEP
mysql
user960928
user960928

Reputation:

How can I return a result set with the values of a field as column headers?

Here's a rough idea of what I have:

+---------------+   +-----------------+    +---------------+
| customers     |   | transactions    |    | branches      |
+---------------+   +-----------------+    +---------------+
| customerid    |   | orderid         |    | branchid      |
|               |   | customerid (FK) |    |               | 
|               |   | branchid (FK)   |    |               | 
+---------------+   | datetime        |    +---------------+
                    +-----------------+

How would I create a query which pulls in the count of transactions per day/week/etc. but for each branchid separately?

I have tried an union such as this (count for all Saturdays only). Which gives a result but not in the format desired.

SELECT COUNT(orderid) as count1 FROM transactions WHERE WEEKDAY(datetime) = 5 
AND branchid = 'branch1' 
UNION 
SELECT COUNT(orderid) as count2 FROM transactions WHERE WEEKDAY(datetime) = 5 
AND branchid = 'branch2'

returns:

+------------+
|   count1   |
+------------+
|        152 |
|         48 |
+------------+

Whereas I would like the data to be formatted as follows:

+------------+------------+
|   count1   |   count2   |
+------------+------------+
|        152 |         48 |
|            |            |
+------------+------------+

Does anyone have any hints on how this can be done? Thanks in advance!

Upvotes: 0

Views: 104

Answers (3)

Nightwolf
Nightwolf

Reputation: 951

It is strange that you want it as columns and not rows but I believe this should do what you require:

SELECT
(SELECT COUNT(orderid) as count1 FROM transactions WHERE WEEKDAY(datetime) = 5 
AND branchid = 'branch1'),
(SELECT COUNT(orderid) as count1 FROM transactions WHERE WEEKDAY(datetime) = 5 
AND branchid = 'branch2')

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270637

There are a few ways to accomplish this. Using the UNION you already have, you can build a pivot query around a subquery. This version adds a static name to each of the columns in your original UNION and uses them to differentiate a CASE statement on the outer query.

In your case, it could be done with subselects inside the SELECT list, however this method is more extensible to other kinds of pivot queries and is the more general convention for achieving them.

SELECT 
  SUM(CASE WHEN name = 'count1' THEN counts ELSE 0 END) AS count1,
  SUM(CASE WHEN name = 'count2' THEN counts ELSE 0 END) AS count2
FROM (
   SELECT 'count1' as name, COUNT(orderid) as counts FROM transactions WHERE WEEKDAY(datetime) = 5 
     AND branchid = 'branch1' 
   UNION 
   SELECT 'count2' as name, COUNT(orderid) as counts FROM transactions WHERE WEEKDAY(datetime) = 5 
     AND branchid = 'branch2'
) subq

Upvotes: 2

dweiss
dweiss

Reputation: 832

In the SELECT section, use a CASE and check if it is branch1 or branch2. You can define separate columns for each in the SELECT. And you don't need to do a UNION -- you can do a GROUP BY branchid WHERE branchid in ('branch1','branch2'). Make sense?

Upvotes: 1

Related Questions